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
2
Answers
This query will fetch the JSON object from the array in the JSON column where the category is ‘B’.
This seems horribly inefficient but you can use JSON_TABLE to transform the array into rows:
Here’s a fiddle