Functioning postgres query not working in JPA-CodePudding

I've been trying to write a working postgres query in JPA, but I'm getting errors in Intellij and when I run Spring it fails with a generic validation exception.

This is my query in postgres

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";

This is my @Query code

@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\"")

In this line:

cast(log.header ->> 'company' as json)  AS \"Company\"

in the operator --> the IDE indicates:

<expression> expected, got '>'

LOWER(cast(log.header as json) ->> 'user') AS \"User\"

In this line:

"MIN (log.dateRes) AS \"First CN\","

The IDE indicates:

identifier expected, got '"First CN"'

the same for the each identifier until the from

And in this line

"INNER JOIN SegCompanyEntity c ON (c.companyName = ( \"header\" :: json ) ->> 'company') "

After the ON the IDE indicates

'(', , FUNCTION or identifier expected, got '('

I have gone through many posts about JPA and native queries but none seem to be similar to my problem.

CodePudding user response：

The query you define in the jpa-@Query annotations is by default a JPQL query. JPQL doesn't understand Postgres (or any other DB) specific features e.g. Postgres' Json(b) type.

There is a flag nativeQuery in the @Query annotation. You can set this to true -- then your query won't be interpreted as JPQL but as native SQL. Obviously, this way, the query will only work with postgres.

@Query(
  nativeQuery = true,
  value = "SELECT..."
)