JPQL on embedded Map

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.

Here you can find more details:

If the underlying column type is a JSON-like DDL type, then the JPQL functions key and value won’t work, as these functions only work if you map to tables. You will have to use the proprietary JSON functions of your DBMS(MySQL :: MySQL 8.0 Reference Manual :: 12.18.3 Functions That Search JSON Values).

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.

1 Like

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.

additional-table

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.

1 Like

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();
}