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?

@Shadow sample data in criteria column which is of data type json is {"industries": ["1"], "locations": ["1", "2"]} — adam78
– adam78, Commented Jun 26, 2017 at 20:18
It seems like yes, you should be able to compare the objects and they should evaluate to true based on the docs: dev.mysql.com/doc/refman/5.7/en/json.html#json-comparison — kjones
– kjones, Commented Jun 26, 2017 at 20:27
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] — cwallenpoole
– cwallenpoole, Commented Jun 26, 2017 at 20:56
Two JSON arrays are equal if they have the same length and values in corresponding positions in the arrays are equal., see 11.6 The JSON Data Type::Comparison and Ordering of JSON Values::Array. — wchiquito
– wchiquito, Commented Jun 27, 2017 at 9:59

Kevinleary.net · Accepted Answer · 2018-10-04 18:03:27Z

13

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.

answered Oct 4, 2018 at 18:03

Kevinleary.net

9,8484 gold badges59 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Omar Ghorbel Over a year ago

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

mixel · Accepted Answer · 2020-06-30 11:18:25Z

10

You can use CAST() function:

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

answered Jun 30, 2020 at 11:18

mixel

26k14 gold badges130 silver badges170 bronze badges

Comments

ax7ster · Accepted Answer · 2020-10-16 14:26:28Z

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.

kmiku7 · Accepted Answer · 2022-07-19 06:16:42Z

1

You can also do it like this:

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

And I don't think you can compare objects ignoring the order of array items since its conflicts with the JSON array definition:

A JSON array contains zero, one, or more ordered elements, separated by a comma.

answered Jul 19, 2022 at 6:16

kmiku7

212 bronze badges

Collectives™ on Stack Overflow

Mysql - How to compare two Json objects?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related