Hibernate search : JSONB columns

Question

We are currently using columns with type Jsonb in PostgreSQL and using Hibernate-types for mapping and insert/update (which is working very well so far) , the only limitations that we are finding right now is related to search with filters on properties of the JSON document (between two dates, ,== , <= , >= , etc ).

Q1 : Is there any way to use Hibernate for querying JSON documents ? Q2 : Is it a good idea to use Hibernate-search to update Elasticsearch and then use Lucene syntax to query ?

Answer 1

Hard to address Q2 without knowing what your requirements are exactly. IMO it's always a good idea to implement search using a dedicated solution such as Elasticsearch (through Hibernate Search of course), but I may a little bit biased :) If you're fine with using PostgreSQL's non-standard features and those features are enough (e.g. you don't really want to use full-text search or faceting), then Hibernate Search + Elasticsearch may be overkill. I'd argue you probably should be using Elasticsearch's advanced full-text search, but to each their own.

The question really is: does PostgreSQL provide a syntax to do what you want, i.e. extract a value from the JSON and apply an operator to it? That's likely, though I'm not familiar enough with JSON in PostgreSQL to give you that syntax.

Once you found the proper syntax, you can use it in HQL (Hibernate ORM's extension of the JPA's query language, JQPL). Either:

• [ORM 6.0+ only] by using the sql() function in your HQL, i.e. sql('<put some SQL here, using ? to represent arguments>', <put comma-separated arguments here>). Hibernate ORM will just insert the proper SQL into the query it sends to the database.
• by declaring custom HQL functions and calling these functions in you (HQL) query.

Of course, if necessary, you can also fall back to native SQL for your whole query, though then mapping the results back to managed entities will prove a bit more cumbersome.