CODEWITHSUNDEEP
sqlmysqlmysql-jsonjson-extract
Bjarke Kingo
Bjarke Kingo

Reputation: 425

Extracting only the JSON_OBJECT in a JSON array where the object matches condition

I have a table with a JSON column. The JSON column holds for each row, a JSON array with JSON objects such as this

[{"my_value": 42, "category": "A"}, {"my_value": 100, "category": "C"}, {"my_value": 15, "category": "B"}, {"my_value": 123, "category": "D"}]

I want to select all the rows from the table, but only select the part of the JSON column that matches the condition (category='B'), such that in this case I'd get the JSON object {"my_value": 15, "category": "B"} Note that the JSON object containing category B could exist in any index

Upvotes: 0

Views: 73

Answers (1)

user1191247
user1191247

Reputation: 12973

This seems horribly inefficient but you can use JSON_TABLE() to transform the array into rows:

SELECT test.id, temp.obj
FROM test
LEFT JOIN
    JSON_TABLE(
        json_data,
        '$[*]' COLUMNS(
            obj JSON PATH '$',
            category CHAR(1) PATH '$.category'
        )
    ) AS temp ON temp.category = 'B';

Here's a fiddle

Upvotes: 1

Related Questions