Jooq Multiset generates sql query with Postgis geometry coordinates instead of hex value

Question

I am using the multiset constructor to fetch a related set of locations of an event. The location table uses the geometry Postgis type so I can abstract it if it's a Point, Line or Area, saving it as a hex representation.

This is the table definition

CREATE TABLE events.locations(
    id bigint not null PRIMARY KEY,
    event_id varchar not null references events.generic_event(id),
    location geometry not null
);

And this is a sample content

id	event_id	location
1	1	01010000007432F387985F434064E6BB885C4C3BC0
2	1	0101000000059415A95C9145406CEA0B7D9C3238C0

When I run this select and nested multiset

dsl.select(GENERIC_EVENT.asterisk(),
    multiset(
             select(LOCATIONS.LOCATION)
             .from(LOCATIONS)
             .where(LOCATIONS.EVENT_ID.eq(GENERIC_EVENT.ID))
    ))
    .from(GENERIC_EVENT)
    .fetch()

Jooq generates this query

select "events"."generic_event".*,
       (select coalesce(jsonb_agg(jsonb_build_array("v0")), jsonb_build_array())
            from (
                select "events"."locations"."location" as "v0"
                from "events"."locations"
                where "events"."locations"."event_id" = "events"."generic_event"."id"
            ) as "t"
        ) 
from "events"."generic_event";

Query result with the event id and the locations as json

[[{""type"": ""Point"", ""coordinates"": [41.64214096573672, -4.777311334633507]}], [{""type"": ""Point"", ""coordinates"": [41.64778522299253, -4.710878384400815]}], [{""type"": ""Point"", ""coordinates"": [38.746842378376556, -27.29828695857431]}], [{""type"": ""Point"", ""coordinates"": [43.13564027360557, -24.19770032444869]}]]

My problem is I store the geometry type as hex string and I expect to fetch it in the same way, as I've registered a custom binding to work with hex. For queries that only fetch the geometry it works fine and I get the hex, but with the multiset, the generated query returns the coordinates so I get an error when the binder tries to build the java type.

Any way to overcome this issue?

Substitute in your query this `select "events"."locations"."location" as "v0"` with this `select ST_AsBinary("events"."locations"."location") as "v0"` — Pepe N O, Jun 13 '23 at 18:53
The pg function that I need is `st_ashexewkb(location)` but the query is generated by jooq, so I need to change it from jooq if it is possible — jorge-plaza, Jun 14 '23 at 07:49
I'm using version 3.18.4 Open Source Edition. I have a custom binding for the geometry types — jorge-plaza, Jun 15 '23 at 08:15

score 0 · Answer 1 · answered Jun 14 '23 at 18:32

This bug has been fixed in jOOQ 3.18.0 and 3.17.6 to add support for the GEOMETRY type in MULTISET emulations:

https://github.com/jOOQ/jOOQ/issues/14195

Please note that GEOMETRY support is a feature of the commercial jOOQ editions, so this bugfix isn't available in the jOOQ Open Source Edition.

Jooq Multiset generates sql query with Postgis geometry coordinates instead of hex value

1 Answers1