CODEWITHSUNDEEP
mysqljsonjoinleft-join
meallhour
meallhour

Reputation: 15571

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

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

Upvotes: 1

Views: 490

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

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

Upvotes: 1

Related Questions