Is it possible to write JPQL to search over embedded Map?
For example we have Article entity having Map<String, String> ‘data’ which is converted to JSON using @Converter. This means no additional table is created, only additional column to store JSON.
Now I have to write a query which will filter Articles by keys and values from map. Here is basic example:
SELECT DISTINCT a
FROM Article a
WHERE KEY( a.data ) LIKE 'N%' AND LOWER( a.data ) LIKE LOWER( 'Z%' )
Also I would accept Query DSL solution for the same query - which I also tried to create, but no success.
Something like this maybe: where json_search( a.data, 'one', lower( :name ), null, '$..*' ) is not null
Your query is especially hard though since you don’t know the exact key names you want to filter on. Either way, you will have to figure this out by yourself with the MySQL documentation or ask in the MySQL community, as I don’t know how this is best done.
From a Hibernate perspective, you should be able to just call these functions.
Thanks a lot! So far it looks like I’ll stick to additional table rather than overengineering JSON field.
For the most of searches I use Lucene and this one is to use with autocomplete where I need substrings from one to ‘n’ characters in length and Lucene is not the best tool for such a case.
I don’t know if I would say using JSON here is overengineered. I just think that your use case, filtering JSON key-value-pairs where the key and the value have to match a pattern, is a bit complicated to implement. We can certainly make some things easier, like extracting values for multiple (statically known) keys through dedicated functions in Hibernate, but that wouldn’t help in your particular case AFAIU.
Hi!
I found slightly better solution - stored procedure.
It’s more portable to some other database because MySQL specific thing “json_search” is now wrapped inside stored procedure and Java part is only call to this procedure. Every new database should implement “findByBeginsWith()” and Java part is always the same or at least always almost the same.
Thanks a million for hints!
MySQL part
-- IMPORTANT: set data access to "Read SQL data" or else you'll get an exception
CREATE PROCEDURE findByBeginsWith( IN namePart VARCHAR(255) )
BEGIN
SELECT DISTINCT c.*
FROM city c
WHERE
JSON_SEARCH( c.json, 'all', CONCAT( namePart, '%' ) )
IS NOT NULL;
END
Java entity part
@NamedStoredProcedureQuery(
name = "findByBeginsWith",
procedureName = "findByBeginsWith",
resultClasses = { LinkedList.class },
parameters = {
@StoredProcedureParameter(
name = "namePart",
type = String.class,
mode = ParameterMode.IN
)
}
)
@Entity
public class City {
...
}
Repository part
public List<City> findByBeginsWith(final String namePart) {
final StoredProcedureQuery query =
this.entityManager.createStoredProcedureQuery(
"findByBeginsWith", City.class
)
.registerStoredProcedureParameter(
"namePart",
String.class,
ParameterMode.IN
)
.setParameter("namePart", namePart);
query.execute();
// Warning: Type safety: The expression of type List
// needs unchecked conversion to conform to List<City>
return query.getResultList();
}
