CODEWITHSUNDEEP
mysqlsqlarraysjsonselect
Fernando Scalia
Fernando Scalia

Reputation: 31

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

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.

Upvotes: 1

Views: 410

Answers (2)

GMB
GMB

Reputation: 222622

You can use json_search():

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

Upvotes: 1

Akina
Akina

Reputation: 42738

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

Upvotes: 1

Related Questions