Jooq Multiset查询返回PostGIS坐标而非十六进制值的问题

问题根源在于：当使用Multiset生成JSON聚合查询时，PostgreSQL会自动将geometry类型序列化为GeoJSON坐标格式，而非触发你注册的自定义Binding返回十六进制值——因为JSON聚合操作在数据库层面完成，绕过了JDBC结果集层面的Binding处理。

解决方案：显式转换为十六进制字符串 #

在Multiset的子查询中，直接调用PostGIS的ST_AsHexEWKB函数将geometry字段转换为十六进制字符串，这样JSON聚合时会存储字符串值，而非坐标JSON。

修改后的Java查询代码

使用原生SQL函数方式：

dsl.select(GENERIC_EVENT.asterisk(),
    multiset(
             select(field("ST_AsHexEWKB({0})", String.class, LOCATIONS.LOCATION))
             .from(LOCATIONS)
             .where(LOCATIONS.EVENT_ID.eq(GENERIC_EVENT.ID))
    ))
    .from(GENERIC_EVENT)
    .fetch()

如果引入了jOOQ的PostGIS扩展依赖，可直接调用封装好的函数：

import org.jooq.impl.PostGIS;

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

原理说明 #

通过ST_AsHexEWKB显式转换后，子查询返回的是字符串类型的十六进制值，JSON聚合时会直接保留该字符串，后续你的自定义Binding（或客户端逻辑）可以正常将其转换为对应的Java类型，避免了数据库自动序列化GeoJSON导致的类型转换错误。

内容的提问来源于stack exchange，提问作者jorge-plaza