MySQL 8+ JSON Query to extract one value from an array

Question

I'm trying all sort of possibilities to extract a single element in a JSON column that contains an array. Let me give the example:

Database:
id | info
---------
1  |{"name": "aaa", "colors": ["a","b"]}
2  |{"name": "bbb", "colors": ["c","d"]}
3  |{"name": "ccc", "colors": ["e","f"]}

What I need is to have a similar query, like: select name, color from info where color = a;

And this should return: "aaa", "a"

The problem that I'm stuck is that I cannot search in the array without a fix index, but I need to be able to query the database without fix index.

Consider to use a relational design, when you use a relational database system. — Paul Spiegel
– Paul Spiegel, Commented Jun 16, 2020 at 12:57
And this should return: "aaa", "a" What is the point of output a well-known literal "a" that is used as the filtering condition? — Akina
– Akina, Commented Jun 16, 2020 at 17:30

Akina · Accepted Answer · 2020-06-16 11:50:01Z

1

SELECT info->"$.name" AS `name`
FROM test
WHERE JSON_CONTAINS(info, '"a"', '$.colors');

answered Jun 16, 2020 at 11:50

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Comments

GMB · Accepted Answer · 2020-06-16 13:10:15Z

1

You can use json_search():

select info ->> '$.name' as name, 'a' color
from mytable 
where json_search(info ->> '$.colors', 'one', 'a') is not null

edited Jun 16, 2020 at 13:10

answered Jun 16, 2020 at 11:36

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

MySQL 8+ JSON Query to extract one value from an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related