CODEWITHSUNDEEP
mysqlarraysjsonsearch
xxllxx666
xxllxx666

Reputation: 343

How to use MySQL to query on json array

I have a table as shown below:

CREATE TABLE IF NOT EXISTS `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` json NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_test` (`id`, `content`) VALUES (1, '[{"name": "vabcx", "value": 100}, {"name": "uabcw", "value": 150}, {"name": "xxx", "value": 110}]');
INSERT INTO `tb_test` (`id`, `content`) VALUES (2, '[{"name": "jabcl", "value": 130}, {"name": "xabcf", "value": 150}, {"name": "uuu", "value": 110}]');
INSERT INTO `tb_test` (`id`, `content`) VALUES (3, '[{"name": "pabcg", "value": 100}, {"name": "qabct", "value": 100}, {"name": "hhh", "value": 300}]');

And I have two requirements:

  1. Find the rows whose name contains "abc" and the value of all names containing "abc" is greater than 120. i.e., for the above table, select the rows with id=2.
  2. Find the rows whose name contains "abc" and at least one of the names containing "abc" has a value greater than 120. i.e., for the above table, select the rows with id=1 and id=2.

I tried the following sql, but all 3 rows are selected because name and value are handled separately.

mysql> SELECT * FROM tb_test WHERE content->'$**.name' LIKE '%abc%'and content->'$**.value' > 120;
+----+---------------------------------------------------------------------------------------------------+
| id | content                                                                                           |
+----+---------------------------------------------------------------------------------------------------+
|  1 | [{"name": "vabcx", "value": 100}, {"name": "uabcw", "value": 150}, {"name": "xxx", "value": 110}] |
|  2 | [{"name": "jabcl", "value": 130}, {"name": "xabcf", "value": 150}, {"name": "uuu", "value": 110}] |
|  3 | [{"name": "pabcg", "value": 100}, {"name": "qabct", "value": 100}, {"name": "hhh", "value": 300}] |
+----+---------------------------------------------------------------------------------------------------+

In adderation, for requirement 2, I don't want to use the following sql because there may be $[4], $[5]...... in the future.

SELECT * FROM tb_test WHERE content->'$[0].name' LIKE '%abc%'AND content->'$[0].value' > 120 OR content->'$[1].name' LIKE '%abc%'AND content->'$[1].value' > 120 OR content->'$[2].name' LIKE '%abc%'AND content->'$[2].value' > 120;

I also know that storing the content as the table below will solve the problem.

CREATE TABLE IF NOT EXISTS `tb_test01` (
  `ass_id` bigint(20) unsigned NOT NULL COMMENT 'associated ID',
  `name` VARCHAR(32) NOT NULL,
  `value` int(11) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

But I want to know how to use sql and json, or any other good way to solve my problem.

I've learned some json search functions from here.

I'm looking forward to your help and thank you in advance.

Upvotes: 0

Views: 222

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

You can use JSON_TABLE to get the name/value pairs into rows and columns:

select id, j.* from tb_test cross join json_table(content, '$[*]' columns (
  name varchar(20) path '$.name', 
  value int path '$.value')) as j;
+----+-------+-------+
| id | name  | value |
+----+-------+-------+
|  1 | vabcx |   100 |
|  1 | uabcw |   150 |
|  1 | xxx   |   110 |
|  2 | jabcl |   130 |
|  2 | xabcf |   150 |
|  2 | uuu   |   110 |
|  3 | pabcg |   100 |
|  3 | qabct |   100 |
|  3 | hhh   |   300 |
+----+-------+-------+

Then you can use a WHERE clause to select the rows you want.

Upvotes: 2

Related Questions