Problem in getting object from JSONArray in PostgreSQL using Hibernate

Question

I want to retrieve an object from a JsonArray of query return value from PostgreSql database using hibernate in spring boot application. But i am facing an exception with '>' is unexpected token, though my query working perfectly in pgAdmin4 Query Tool, the following my code snippet.

@PersistenceContext
private EntityManager entityManager;

@Transactional
public String getItemById(Long id) {
    String result = (String) entityManager.createQuery("SELECT JSON_AGG(items)->>0 AS item FROM items WHERE id=:id").setParameter("id", id)
            .getResultList().get(0);

    System.out.println(result);
    return result;
}

Exception as follows:

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: > near line 1, column 24 [SELECT JSON_AGG(items)->>0 AS item FROM items WHERE id=:id]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: > near line 1, column 24 [SELECT JSON_AGG(items)->>0 AS item FROM items WHERE id=:id]
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:367)

please help to achieve this or suggest me work arounds without changing query. Thanks in advance.

Answer 1

The API method you are using (entityManager.createQuery(String)) is to execute JPQL queries as stated in the javadoc:

Create an instance of Query for executing a Java Persistence query language statement.

The query you want to execute is a SQL query. You should use the createNativeQuery(String) method on EntityManager.