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

Question

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.

What version of MySQL are you using? And what exactly do you mean by `Items.Row1`, `Items.Row2` etc — Salman A, Oct 27 '21 at 14:04
Mysql version : mysql 8.0.18 & mariaDB 10.4.10 it mean the content of JSON field in each row inserted in the table — Chargui Taieb, Oct 27 '21 at 14:08

Salman A · Accepted Answer · 2021-10-27T15:15:37.730

2

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.

edited Oct 27 '21 at 15:15

answered Oct 27 '21 at 14:22

Salman A

262,204
82
430
521

Hey sorry to bother again i know this is an answered question but i just want to know how can i tweak it so i can select all the transactions that they had item start with "MH%" – Chargui Taieb Jan 13 '22 at 10:21
That would be a different question. I would use `JSON_TABLE`. – Salman A Jan 13 '22 at 12:07
can you point me please ? – Chargui Taieb Jan 13 '22 at 14:13

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

1 Answers1