CODEWITHSUNDEEP
mysqljson
orion92
orion92

Reputation: 55

Querying JSON column in MySQL with id

I have a table in MySQL with data in a column in the following format:

[{"type_id":1,"price":50},{"type_id":3,"price":60}]

I need to find out price of the item based on its id. For example, I need to find out price of an item with type_id = 3

I have tried:

select JSON_EXTRACT(JSONColumn, '$[*].price') as prices, JSON_EXTRACT(JSONColumn, '$[*].type_id') as quantities from Items where ItemId = 123
and json_contains(JSONColumn, '{"type_id" : 3}')

This is not working. Can someone specify the correct way of querying json data?

Upvotes: 0

Views: 24

Answers (1)

Akina
Akina

Reputation: 42728

SELECT test.id, jsontable.price
FROM test
CROSS JOIN JSON_TABLE (test.val,
                       '$[*]' COLUMNS (type_id INT PATH '$.type_id',
                                       price INT PATH '$.price')) jsontable
WHERE jsontable.type_id = 3;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=baa0a24a4bbf10ba30202c7156720018

Upvotes: 1

Related Questions