How to correctly use `JSON_TABLE` to fetch matching names from Json Array within MySQL8?

Question

I am using MySQL 8 and my table structure looks as below:

CREATE TABLE t1 (id INT, `group` VARCHAR(255), names JSON);

I am able to correctly insert records using the following INSERT statement:

INSERT INTO t1 VALUES
(3001, 'group1', '[{"name": "name1", "type": "user"}, {"name": "name2", "type": "user"}, {"name": "techDept", "type": "dept"}]'),
(3002, 'group2', '[{"name": "name1", "type": "user"}]');

The JSON format has two types - user and dept

I want to fetch all groups having a matching name to the name1 from Json Array and I have written following query:

SELECT t1.* FROM t1, JSON_TABLE(users, "$.users[*].type" COLUMNS(f INT PATH '$')) as user WHERE user.name = 'name1'

I am getting below error:

Unknown column name

Desired Output:

group1
group2

Bill Karwin · Accepted Answer · 2021-03-21 00:34:06Z

1

This is tested on 8.0.23 using your data:

SELECT t1.`group` 
FROM t1, JSON_TABLE(names, '$[*]' 
    COLUMNS(
      name TEXT PATH '$.name', 
      type TEXT PATH '$.type')
    ) AS j 
WHERE j.name = 'name1'

Output:

+--------+
| group  |
+--------+
| group1 |
| group2 |
+--------+

edited Mar 21, 2021 at 0:34

answered Mar 21, 2021 at 0:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Bill Karwin Over a year ago

If you have this much trouble using the JSON functions, I strongly suggest you use MySQL in the normal fashion, with data elements in individual rows and columns.

Collectives™ on Stack Overflow

How to correctly use `JSON_TABLE` to fetch matching names from Json Array within MySQL8?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related