Hibernate Community • View topic - custom dialect to support the PostgreSQL json operators

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

custom dialect to support the PostgreSQL json operators

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

jordanglassman

Post subject: custom dialect to support the PostgreSQL json operators

Posted: Mon Oct 31, 2016 11:22 am

Newbie

Joined: Mon Oct 31, 2016 11:05 am
Posts: 2

PostgreSQL added the ability to query json/jsonb type columns with operators like this:

Code:

SELECT * FROM user u WHERE customerInfo @> :foo

where customerInfo is a jsonb column.

When investigating adding a custom SQLFunction implementation to my dialect, it seems like the

Code:

registerFunction

method and

Code:

SQLFunction

methods assume a function/operator of the form

Code:

function(arg1, arg2)

. Is there a way to implement a custom dialect for a function of the form

Code:

arg1 operator arg2

?

As a side note, I mention that I tried to do this using a

Code:

NativeNamedQuery

, but Hibernate would not recognize the named param, I assume because it believes the query is malformatted:

Code:

[Server:myserver]     Caused by: java.lang.IllegalArgumentException: Parameter with that name [foo] did not exist
[Server:myserver]    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:503)

Thanks!

Top

vlad

Post subject: Re: custom dialect to support the PostgreSQL json operators

Posted: Mon Oct 31, 2016 1:37 pm

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

You can't use JPQL or HQL since the arrow operator is not supported anyway. You need to use SQL for querying JSON since there is no standard SQL way of doing it.

Top

jordanglassman

Post subject: Re: custom dialect to support the PostgreSQL json operators

Posted: Tue Nov 01, 2016 9:17 am

Newbie

Joined: Mon Oct 31, 2016 11:05 am
Posts: 2

I see, thanks. By "arrow operator" you mean the ">" in "@>"?

Is there a related, syntactical reason that NativeNamedQueries shouldn't work?

I briefly tried something like:

Code:

@Entity
@NamedNativeQueries(
      value = { @NamedNativeQuery(name = User.FIND_ALL_BY_CUSTOMER_INFO,
            query = "SELECT * FROM user u WHERE customerInfo @> :foo",
            resultClass = User.class) })
public class User ....

but was consistently getting the error:

Code:

[Server:server1] Caused by: java.lang.IllegalArgumentException: Parameter with that name [foo] did not exist
[Server:server1]    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:503)

Top

vlad

Post subject: Re: custom dialect to support the PostgreSQL json operators

Posted: Tue Nov 01, 2016 11:46 am

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

That's strange. I tested this query on 5.2.4 and it works just fine:

Code:

participants = entityManager.createNativeQuery(
                "select jsonb_pretty(p.ticket) " +
                "from participant p " +
                "where p.ticket ->> 'price' > :price")
.setParameter("price", "10")
.getResultList();

If you have a replicating test case, you should open a new Jira issue.

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum