Mysql - How to compare two Json objects?

What is the syntax to compare an entire MySql json column with a json object?

The following doesn't work:

select count(criteria) from my_alerts where criteria = '{"industries": ["1"], "locations": ["1", "2"]}'

I get a count of 0 even when the criteria column has value {"industries": ["1"], "locations": ["1", "2"]}

correct me if I'm wrong but two JSON objects are equal if they have the same set of keys, and each key has the same value in both objects. The order of the keys and values is ignored. So the following should be the same?

 {"industries": ["1"], "locations": ["1", "2"]} = {"locations": ["2", "1"], "industries": ["1"]}

* Update *

I've managed to get it working by casting to json as follows:

select count(criteria) from my_alerts where criteria = CAST('{"industries": ["1"], "locations": ["1", "2"]}' AS JSON)

However whilst the order of the keys is ignored during the comparison the order of the values is still compared. So the following is falsy:

{"locations": ["1", "2"]} = {"locations": ["2", "1"]}

Is there any way to force the comparison to ignore order of the values aswell?

回答1:

You can do this using JSON_CONTAINS:

SELECT COUNT(criteria) 
FROM my_alerts 
WHERE JSON_CONTAINS(criteria,'{"industries": ["1"], "locations": ["1", "2"]}')

This perform a comparison that ignores the order of the values, which is critical because MySQL will re-order JSON properties for efficiency on INSERT.

回答2:

You can use CAST() function:

SELECT count(criteria)
FROM my_alerts 
WHERE criteria = CAST('{"industries": ["1"], "locations": ["1", "2"]}' AS JSON)