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.

• Seelenvirtuose: What is that JSON_AGG(items)->>0 syntax?
• MAREESKANNNAN RAJENDRAN: A postgres utility function to convert query result to json array of objects.
• Seelenvirtuose: I know what the JSON_AGG function is. But what is the ->>0 being for? I don't know that syntax. And postgres obviously also ...
• MAREESKANNNAN RAJENDRAN: to retrieve first object from json array

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.