Search code examples
mysqlsqljsonmysql-json

Search an array of objects for specific key having a certain value

I want to retrieve all rows from a table in MySQL table where there is a JSON column called items with content like this:

[{"code":"MH005","qte":1,"totalpriceItem":"28.00"},{"code":"MH027","qte":1,"totalpriceItem":"28.00"}]
[{"code":"MH027","qte":1,"totalpriceItem":"30.00"}]
[{"code":"MH024","qte":1,"totalpriceItem":"28.00"},{"code":"MH028","qte":1,"totalpriceItem":"28.00"},{"code":"MH027","qte":1,"totalpriceItem":"28.00"}]
[{"code":"MH028","qte":1,"totalpriceItem":"30.00"}]

Now i want to be able to select all the row where the key code has the value MH027.

I've tried this but without success:

SELECT *
FROM `transactions`
WHERE JSON_CONTAINS(`item`, 'MH027', '$[*].code')

Any help is much appreciated.

Solution

  • If I understand correctly you need to use JSON_CONTAINS as follows:

    SELECT *
FROM transactions
WHERE JSON_CONTAINS(item, '{"code": "MH027"}', '$')

    The second parameter must be valid JSON. You could use JSON_OBJECT('code', 'MH028') if the value is dynamic or could contain special characters.