Search for exact matches to JSON in MySQL

MySQL 5.7 and later contain the following table JSON:

CREATE TABLE `p`(
  `id`int(11) NOT NULL AUTO_INCREMENT,
  `parameter`json NOT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `p`(`id`,`parameter`)VALUES(1,'[{\"a\":\"hoge\"',\"parameter\":{\"m\":4,\"i\":5},{\"a\":\"fuga\"',\"parameter\":{\"apple\":\"red\"}}}]');

What should I do if I want to search exactly the same as the parameter when I receive new data?I tried using JSON_CONTAINS, but this is a partial search, so if I get [{\"a\":\"fuga\"',\"parameter\":{\"apple\":\"red\"}}], it also hits 1.However, you only want [{\"a\":\"hoge\"',\"parameter\":{\"m\":4,\"i\":5},{\"a\":\"fuga\"',\"parameter\":{\"apple\":\"red\"}}] to come.

I also tried string search in JSON_EXTRACT+CAST before, but the array taken out by JSON_EXTRACT was not successful because the order of keys was broken.

mysql sql json

2022-09-30 11:41

1 Answers

First of all, the json in the questionnaire is one missing }, so it is malformed as json.

After fixing it and testing it, as OOPer said, by specifying conditions for json type data with =, we were able to select the data by comparing the json structure as follows.

with entries(id, parameter) as(
  select1, cast('[{"a":"hoge", "parameter":{"m":4, "i":5}}, {"a":"fuga", "parameter":{"apple":"red"}}]'as json)
)
select * from entries
  where parameter=cast('[{"a":"hoge", "parameter":{"i":5, "m":4}}, {"parameter":{"apple":"red"}, "a":"fuga"}]'as json)
\G

Output

*****************************************************
       id:1
parameter: [{"a":"hoge", "parameter":{"i":5, "m":4}, {"a":"fuga", "parameter":{"apple":"red"}}]

2022-09-30 11:41

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656