可运行的PostgreSQL查询在JPA中无法使用的问题排查

原PostgreSQL可执行查询 #

SELECT
        ( "header" :: json ) ->> 'company' AS "Company",
        LOWER(( "header" :: json ) ->> 'user') AS "User",
        MAX ( ( "header" :: json ) ->> 'version' ) AS "Version",
        MIN ( date_res ) AS "First CN",
        MAX ( date_res ) AS "Last CN",
        SUM ( CASE WHEN url = '/url-1' AND status = 200 THEN 1 ELSE 0 END ) AS "Survey",
        SUM ( CASE WHEN url = '/url-2' AND status = 200 THEN 1 ELSE 0 END ) AS "Work",
        SUM ( CASE WHEN url = '/url-3' AND status = 200 THEN 1 ELSE 0 END ) AS "Home",
        SUM ( CASE WHEN url = '/url-4' AND status = 200 THEN 1 ELSE 0 END ) AS "DeploySurvey",
        SUM ( CASE WHEN url = '/url-5' AND status = 200 THEN 1 ELSE 0 END ) AS "DeployWork"
FROM
    "public"."seg_ws_log_res"
        INNER JOIN "seg_company" ON ("seg_company"."companyName" = ( "header" :: json ) ->> 'company')
WHERE
        "service" = 'service'
  AND NOT ( "header" :: json ) ->> 'company' IS NULL
  AND NOT ( "header" :: json ) ->> 'user' IS NULL
  AND NOT ( "header" :: json ) ->> 'company' = ''
  AND NOT UPPER(( "header" :: json ) ->> 'user') IN ('USER_1', 'USER_2')
  AND NOT ( "header" :: json ) ->> 'version' = 'Dev'
  AND NOT UPPER(( "header" :: json ) ->> 'company') IN ('text_1','text_2','text_3')
GROUP BY
    "Company",
    "User"
ORDER BY
    "Company",
    "User";

改写后的JPA @Query代码（存在语法错误） #

@Query(value = "SELECT " +
    "cast(log.header as json) ->> 'company' AS \"Company\"," +
    "LOWER(cast(log.header as json) ->> 'user') AS \"User\"," +
    "MAX ( cast(log.header as json) ->> 'version' ) AS \"Version\"," +
    "MIN (log.dateRes) AS \"First CN\"," +
    "MAX ( log.dateRes ) AS \"Last CN\"," +
    "SUM ( CASE WHEN log.url = '/url-1' AND log.status = 200 THEN 1 ELSE 0 END ) AS \"Survey\"," +
    "SUM ( CASE WHEN log.url = '/url-2' AND log.status = 200 THEN 1 ELSE 0 END ) AS \"Work\"," +
    "SUM ( CASE WHEN log.url = '/url-3' AND log.status = 200 THEN 1 ELSE 0 END ) AS \"Home\"," +
    "SUM ( CASE WHEN log.url = '/url-4' AND log.status = 200 THEN 1 ELSE 0 END ) AS \"DeploySurvey\"," +
    "SUM ( CASE WHEN log.url = '/url-5' AND log.status = 200 THEN 1 ELSE 0 END ) AS \"DeployWork\"" +
    "FROM" +
    " SegWsLogResEntity log" +
    "INNER JOIN SegCompanyEntity c ON (c.companyName = ( cast(log.header as json)) ->> 'company') " +
    "WHERE" +
    " log.service = 'service' " +
    " AND NOT cast(log.header as json) ->> 'company' IS NULL " +
    " AND NOT cast(log.header as json) ->> 'user' IS NULL " +
    " AND NOT cast(log.header as json) ->> 'company' = '' " +
    " AND NOT UPPER(cast(log.header as json) ->> 'user') IN ('USER_1', 'USER_2') " +
    " AND NOT cast(log.header as json) ->> 'version' = 'Dev'" +
    " AND NOT UPPER(cast(log.header as json) ->> 'company') IN ('text_1','text_2','text_3')" +
    "GROUP BY" +
    "\"Company\"," +
    "\"User\"" +
    "ORDER BY" +
    "\"Company\"," +
    "\"User\"")

报错详情 #

• 在cast(log.header as json) ->> 'company' AS \"Company\"处，IDE提示：<expression> expected, got '>'
• 在MIN (log.dateRes) AS \"First CN\"及后续带空格的别名处，IDE提示：identifier expected, got '"First CN"'
• 在INNER JOIN的ON子句(c.companyName = ( cast(log.header as json)) ->> 'company')处，IDE提示：'(', , FUNCTION or identifier expected, got '('

修复方案 #

1. 替换JSON操作符为兼容函数 #

JPA解析器无法识别PostgreSQL专属的->>操作符，改用标准JSON函数json_extract_path_text替代：

-- 原写法
cast(log.header as json) ->> 'company'
-- 替换为
json_extract_path_text(cast(log.header as json), 'company')

2. 调整带空格的别名 #

JPA对带空格的别名支持受限，建议改用无空格别名，后续在结果映射时再调整显示名称：

-- 原写法
MIN (log.dateRes) AS \"First CN\"
-- 替换为
MIN(log.dateRes) AS first_cn

3. 修正JOIN子句语法 #

ON子句中多余的嵌套括号会触发解析错误，直接简化表达式：

-- 原写法
INNER JOIN SegCompanyEntity c ON (c.companyName = ( cast(log.header as json)) ->> 'company')
-- 替换为
INNER JOIN SegCompanyEntity c ON c.companyName = json_extract_path_text(cast(log.header as json), 'company')

4. 规范GROUP BY/ORDER BY #

JPA可能无法识别SELECT中的别名用于分组排序，直接重复表达式或使用无空格别名：

-- 原写法
GROUP BY "\"Company\"", "\"User\""
-- 替换为
GROUP BY company, user_name

5. 开启原生查询模式 #

必须添加nativeQuery = true，告知JPA按PostgreSQL原生语法解析这段查询。

修复后的完整@Query代码 #

@Query(value = "SELECT " +
    "json_extract_path_text(cast(log.header as json), 'company') AS company," +
    "LOWER(json_extract_path_text(cast(log.header as json), 'user')) AS user_name," +
    "MAX(json_extract_path_text(cast(log.header as json), 'version')) AS version," +
    "MIN(log.dateRes) AS first_cn," +
    "MAX(log.dateRes) AS last_cn," +
    "SUM(CASE WHEN log.url = '/url-1' AND log.status = 200 THEN 1 ELSE 0 END) AS survey," +
    "SUM(CASE WHEN log.url = '/url-2' AND log.status = 200 THEN 1 ELSE 0 END) AS work," +
    "SUM(CASE WHEN log.url = '/url-3' AND log.status = 200 THEN 1 ELSE 0 END) AS home," +
    "SUM(CASE WHEN log.url = '/url-4' AND log.status = 200 THEN 1 ELSE 0 END) AS deploy_survey," +
    "SUM(CASE WHEN log.url = '/url-5' AND log.status = 200 THEN 1 ELSE 0 END) AS deploy_work" +
    " FROM SegWsLogResEntity log" +
    " INNER JOIN SegCompanyEntity c ON c.companyName = json_extract_path_text(cast(log.header as json), 'company')" +
    " WHERE log.service = 'service'" +
    " AND json_extract_path_text(cast(log.header as json), 'company') IS NOT NULL" +
    " AND json_extract_path_text(cast(log.header as json), 'user') IS NOT NULL" +
    " AND json_extract_path_text(cast(log.header as json), 'company') != ''" +
    " AND NOT UPPER(json_extract_path_text(cast(log.header as json), 'user')) IN ('USER_1', 'USER_2')" +
    " AND json_extract_path_text(cast(log.header as json), 'version') != 'Dev'" +
    " AND NOT UPPER(json_extract_path_text(cast(log.header as json), 'company')) IN ('text_1','text_2','text_3')" +
    " GROUP BY company, user_name" +
    " ORDER BY company, user_name", nativeQuery = true)

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