Mysql - How to compare two Json objects?

Question

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?

Solution 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.

Solution 2

You can use CAST() function:

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

Comments

[cwallenpoole]

In JSON, contents of an array [] are equal IFF the values are the same and are in the same order. [1,2,3] != [3,1,2]

[adam78]

@Shadow sample data in criteria column which is of data type json is {"industries": ["1"], "locations": ["1", "2"]}

[Omar Ghorbel]

May be use JSON_LENGTH beside to make sure that json perfectly equal