How to use MySQL to query on json array

Question

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:

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

Bill Karwin · Accepted Answer · 2021-12-21 17:55:10Z

2

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.

answered Dec 21, 2021 at 17:55

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Akina Over a year ago

The JSON is an array, and the object position may be nesessary, so it may be safe to add rowno FOR ORDINALITY as demonstration.

xxllxx666 Over a year ago

It is a worthwhile idea and I will verify its feasibility with large-scale data, thank you very much.

Collectives™ on Stack Overflow

How to use MySQL to query on json array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related