Question 169922 in PostgreSQL Question posted in
The official documentation can be found here.

Does Hibernate CreateNativeQuery allow JSON cast in Postgresql?

Using Java and Hibernate 6.1.7 Final:

Is it possible to use a JSON key in the select statement when using createNativeQuery()?

Example:

Query<Object[]> query = new SessionManager().fetch()
    .createNativeQuery(
        "select contactid, flag::jsonb->>'priority' as priority from contact", 
         Object[].class);

Stepping through the program, I can see that query.sqlString is:

select contactid, flag:jsonb->>'priority' as priority from contact

Even though query.originalSqlString is:

select contactid, flag::jsonb->>'priority' as priority from contact

Exception:

16-May-2023 11:18:34.801 ERROR [http-nio-65501-exec-8] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions ERROR: syntax error at or near ":"
Position: 31
jakarta.persistence.PersistenceException: Converting org.hibernate.exception.SQLGrammarException to JPA PersistenceException : JDBC exception executing SQL [select
contactid,
flag:jsonb->>’priority’ as priority
from
contact] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:165)
…

Why is the other colon removed?

Tags:

2

Answers


  1. 0

    Do not use the :: cast operator in your native queries. Try a CAST( smth AS target-type) instead:

    select contactid, CAST(flag AS jsonb) ->> 'priority' as priority from contact

    Using a :: cast operator leads to an error since :name is used to represent named parameters in the queries.

    Login or Signup to reply.
  2. 0

    I would report this. It’s true, as Mark states, that :name is supposed to be parsed in native SQL queries in JPA.

    But I would say that we should be easily able to recognize :: and leave it alone.

    So I would call this a bug.

    UPDATE

    So, it turns out that this is something that someone implemented intentionally as an (undocumented) feature. The idea is that you can use a double colon as an escape to write a single colon.

    I must say don’t like it at all, and I doubt it’s a spec-compliant thing to do. I would like to roll it back, but on the other hand it’s been there for quite a while.

    Sooooo, that said, you could write your query like this:

    select contactid, flag::::jsonb->>'priority' as priority from contact

    with four colons.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search