In my database, I have a table orders which contains orders.items in JSON format.
An example of the orders.items cell looks like this:
{
"10": {
"name": "item 1",
"step": "1",
"price": "140",
"amount": "4",
"category": "9"
},
"24": {
"name": "item 2",
"step": "1",
"price": "6.2",
"amount": "1",
"category": "5"
},
"35": {
"name": "item 3",
"step": "1",
"price": "2.9",
"amount": "3",
"category": "1"
},
"37": {
"name": "item 4",
"step": "1",
"price": "3.9",
"amount": "2",
"category": "9"
}
}
I need to extract only items that belong to a specific category.
The expected result for extracting items with category "9" should look like this:
{
"10": {
"name": "item 1",
"step": "1",
"price": "140",
"amount": "4",
"category": "9"
},
"37": {
"name": "item 4",
"step": "1",
"price": "3.9",
"amount": "2",
"category": "9"
}
}
So far, I was able to retrieve all orders.items cells where there is an item with category = "9".
SELECT
`id`,
JSON_EXTRACT(`orders`.`items`,
'$')
FROM
`orders`
WHERE
JSON_CONTAINS(
JSON_EXTRACT(`orders`.`items`,
'$.*.category'),
'"9"'
)