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.