How to query JSON in MySql

Question

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.

Try it with JSON_CONTAINS(zipCodes, :zipCode ). I'm not sure it will work. — coladict
– coladict, Commented Feb 1, 2018 at 14:23

BuZZ-dEE · Accepted Answer · 2021-10-14 14:02:17Z

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

Collectives™ on Stack Overflow

How to query JSON in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related