Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0 
    SELECT jsonb_array_elements(your_json_colum) AS json_obj
FROM your_table
WHERE jsonb_array_elements(your_json_colum)->>'category' = 'B';

    This query will fetch the JSON object from the array in the JSON column where the category is ‘B’.

    Login or Signup to reply.
  2. 0

    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 '$',
            NESTED PATH '$.category' COLUMNS (category CHAR(1) PATH '$')
        )
    ) AS temp ON temp.category = 'B';

    Here’s a fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search