PostgreSQL JSON转换语法在JPA原生查询中报错求助

我有一条在PostgreSQL中可正常运行的查询语句：

SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(geo.*)::json))
FROM (SELECT t.track_id, p.video_id, ST_MakeLine(p.location)
      FROM point as p inner join track as t on p.video_id = t.video_id
      group by p.video_id, t.track_id
      order by track_id) as geo;

但将其改写为JPA原生查询时无法正常运行：

@Query(value = "SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(g.*)::json)) " +
            "FROM (SELECT t.track_id, p.video_id, ST_MakeLine(p.location) " +
            "FROM point as p inner join track as t on p.video_id = t.video_id " +
            "group by p.video_id, t.track_id " +
            "order by track_id) as g",
            nativeQuery = true)

报错信息：

ERROR: syntax error at or near ":"

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(g.*)::json)) FROM (SELECT t.track_id, p.video_id, ST_MakeLine(p.location) FROM point as p inner join track as t on p.video_id = t.video_id group by p.video_id, t.track_id order by track_id) as g] [ERROR: syntax error at or near ":"
Position: 93] [n/a]; SQL [n/a]] with root cause

问题根源是JPA的语法解析逻辑：它会把PostgreSQL类型转换简写::json里的冒号，误识别为参数占位符（比如:param格式的参数标记），导致语法解析错误。

有两种可行的解决方式：

1. 使用标准SQL的CAST函数替代PostgreSQL简写 #

把ST_AsGeoJSON(g.*)::json替换为CAST(ST_AsGeoJSON(g.*) AS json)，修改后的JPA查询如下：

@Query(value = "SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(CAST(ST_AsGeoJSON(g.*) AS json))) " +
            "FROM (SELECT t.track_id, p.video_id, ST_MakeLine(p.location) " +
            "FROM point as p inner join track as t on p.video_id = t.video_id " +
            "group by p.video_id, t.track_id " +
            "order by track_id) as g",
            nativeQuery = true)

这种方式用标准SQL语法实现类型转换，兼容性更强，也避免了转义带来的可读性问题，是优先推荐的方案。

2. 转义冒号（适用于Spring Data JPA 2.x及以上版本） #

如果想继续使用PostgreSQL的简写语法，可以把冒号转义为\\:，让JPA不将其识别为参数占位符：

@Query(value = "SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(g.*)\\:\\:json)) " +
            "FROM (SELECT t.track_id, p.video_id, ST_MakeLine(p.location) " +
            "FROM point as p inner join track as t on p.video_id = t.video_id " +
            "group by p.video_id, t.track_id " +
            "order by track_id) as g",
            nativeQuery = true)

内容的提问来源于stack exchange，提问作者Shumitum