Functioning postgres query not working in JPA

Question

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.

Kristian Ferkić · Accepted Answer · 2022-12-19 13:59:40Z

1

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..."
)

answered Dec 19, 2022 at 13:59

Kristian Ferkić

4984 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Functioning postgres query not working in JPA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related