How to select JSON object from JSON array field of mysql by some condition

Question

I have a table with JSON field which contains an array of JSON objects. I need to select objects by some condition.

Create and fill a table:

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_list JSON
);

INSERT INTO test(json_list) VALUES
("{""list"": [{""type"": ""color"", ""value"": ""red""}, {""type"": ""shape"", ""value"": ""oval""}, {""type"": ""color"", ""value"": ""green""}]}"),
("{""list"": [{""type"": ""shape"", ""value"": ""rect""}, {""type"": ""color"", ""value"": ""olive""}]}"),
("{""list"": [{""type"": ""color"", ""value"": ""red""}]}")
;

Now I need to select all objects with type = color from all rows.

I want to see this output:

id  extracted_value
1   {"type": "color", "value": "red"}
1   {"type": "color", "value": "green"}
2   {"type": "color", "value": "olive"}
3   {"type": "color", "value": "red"}

It would be good to get this too:

id  color
1   red
1   green
2   olive
3   red

I can't change the DB or JSON.

I'm using MySQL 5.7

My current solution

My solution is to cross join the table with some index set and then extract all elements of JSON array.

I don't like it as if possible object count in one array is large it is required to have all indexes till the maximum one. It makes the query slow as it won't stop calculation of JSON value when the end of array is reached.

SELECT 
    test.id,
    JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')),
    ind.ind
FROM
    test
CROSS JOIN
    (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind
WHERE
    JSON_LENGTH(json_list, "$.list") > ind.ind
    AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

It is easy to get only values by changing JSON_EXTRACT path. But is it there a better way?

Edits

Added a check for json_list.list length. This filtered out 67% of derived table rows in this case.

Not much you can do with 5.7. I would limit the JOIN using the length of the array: json_length(json_list->'$.list') > ind.ind — Paul Spiegel
– Paul Spiegel, Commented Jun 4, 2020 at 7:58
@PaulSpiegel checking length should help. Thank you. Will edit my solution. — RusArtM
– RusArtM, Commented Jun 4, 2020 at 8:05

RusArtM · Accepted Answer · 2020-06-08 04:45:24Z

6

So current best solution is mine:

SELECT 
    test.id,
    JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')),
    ind.ind
FROM
    test
CROSS JOIN
    (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind
WHERE
    JSON_LENGTH(json_list, "$.list") > ind.ind
    AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

answered Jun 8, 2020 at 4:45

RusArtM

1,3503 gold badges18 silver badges29 bronze badges

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

Comments

Rubin Porwal · Accepted Answer · 2020-06-08 07:04:00Z

5

SELECT JSON_EXTRACT(json_list, '$.list[*]')
FROM `test`
where JSON_CONTAINS(json_list, '{"type":"color"}', '$.list')

answered Jun 8, 2020 at 7:04

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

3 Comments

RusArtM Over a year ago

Thiw will give json arrays for rows which fit condition: For the example it would give 3 rows instead of 4. Each result will contain all json array elements.This is not what is needed

Vidal Over a year ago

can you provide why this solution answer the question?

钟智强 Over a year ago

This is helpful, why no one is upvotting this answer?

lokendra birla · Accepted Answer · 2023-05-04 10:00:43Z

0

It's working for me here multiple filter use

SELECT id, JSON_EXTRACT(floorplan, '$') FROM table_name WHERE JSON_CONTAINS(floorplan, '{"Bed":0}', '$') OR JSON_CONTAINS(floorplan, '{"Bed":3}', '$');

'[{"Bed": 2, "MinimumRent": 1895}, {"Bed": 3, "MinimumRent": 2325}]'),

answered May 4, 2023 at 10:00

lokendra birla

363 bronze badges

Comments

ysth · Accepted Answer · 2024-05-16 20:56:15Z

0

In mysql 8 (and no one should be running any unsupported and insecure earlier versions), you use JSON_TABLE for this:

select test.id, json_list.value color
from test
join json_table(
  json_extract(test.json_list, "$.list"), "$[*]"
  columns (type text path "$.type", value text path "$.value")
) json_list on json_list.type='color

fiddle

answered May 16, 2024 at 20:56

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Collectives™ on Stack Overflow

How to select JSON object from JSON array field of mysql by some condition

My current solution

Edits

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My current solution

Edits

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related