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?

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

Answer 2

You can use CAST() function:

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

Answer 3

If you are using MySQL 5.7.8 or higher you should be able compare a json column to a json object with the following syntax. The order of inserted keys value pairs doesn't matter.

SELECT json_col = JSON_OBJECT('foo', 'bar', 'color', 'red') FROM table1;

Test this by creating the following table:

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `json_col` JSON NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Insert data into the table. Note the reversed order in the last inserted row. The fifth row has an additional key value pair.

INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"foo\": \"bar\", \"color\": \"red\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"color\": \"blue\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"color\": \"green\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"foo\": \"bar\", \"color\": \"red\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"foo\": \"bar\", \"color\": \"red\", \"baz\": 2}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"color\": \"red\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"color\": \"red\"}');
INSERT INTO `mydb`.`table1` (`json_col`) VALUES ('{\"color\": \"red\", \"foo\": \"bar\"}');

Now by using the following query:

SELECT 
json_col, 
json_col = JSON_OBJECT('foo', 'bar', 'color', 'red'), 
json_col = JSON_OBJECT('color', 'red', 'foo', 'bar') 
FROM table1;

We get:

{"foo": "bar", "color": "red"}  1   1
{"color": "blue"}   0   0
{"color": "green"}  0   0
{"foo": "bar", "color": "red"}  1   1
{"baz": 2, "foo": "bar", "color": "red"}    0   0
{"color": "red"}    0   0
{"color": "red"}    0   0
{"foo": "bar", "color": "red"}  1   1

Note that the order in the json object doesn't matter either. Additional keys matter however.