CODEWITHSUNDEEP
javaarrayshibernate
Noman
Noman

Reputation: 887

How to query JSON in MySql

My database has email as ID and another column zipCode as JSON type. Querying directly in MySQL like this is working:

SELECT * FROM Test WHERE 
    JSON_CONTAINS(testJson, '12345');

So testJson is the JSON type column with JSON arrays like:

[22079, 22310, 12345, 65412, 78954]

I'm trying to replicate the above in Hibernate:

session.createQuery( "from " + ZipCodes.class.getSimpleName() + " where JSON_CONTAINS(zipCodes, zipCodes=:zipCode )")
                .setParameter("zipCode", zipCode)
                .getResultList();

But I'm getting this exception:

unexpected AST node: ( near line 1, column 60 [ where JSON_CONTAINS(zipCodes, zipCodes=:zipCode )]

I don't know how to construct my JSON_CONTAINS clause.

Upvotes: 2

Views: 755

Answers (1)

Zak Aroui
Zak Aroui

Reputation: 31

I had the same error trying to do the same thing. This solution should work easily with your example, because my JSON was more complicated.

Constructing the JSON_CONTAINS in hibernate is slightly different than MySql. So assuming that your zipCodes JSON is the same as the testJson, here is the HQL syntax that worked for me:

JSON_CONTAINS(zipCodes, '12345') = 1

Basically you need to add the = 1, which means the JSON_CONTAINS must evaluate to true.

Note that sometimes you might get an error because of :zipCode (mostly when dealing with collins in complicated JSON). In that case you can replace your value directly in the HQL. Something like this:

session.createQuery( "from " + ZipCodes.class.getSimpleName() + " where JSON_CONTAINS(zipCodes, '"+zipCode+"' )")
                .getResultList();

If your JSON is more complicated, then add a third parameter as the path. More on that here: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains

Upvotes: 1

Related Questions