Querying specific JSON field in MySQL / MariaDB JSON column

Selasa, 3 Disember 2024, 11:55 am0

10 years after MySQL released the native support for JSON data type, only get to use it now .

Previously I use TEXT data type to store JSON string. The main disadvantage of this is we can’t query the specific field inside the JSON document, or add query condition based on values of the fields in the JSON document. Or to update specific field without loading the whole JSON data out.

Let’s say I have a table vehicles that store list of vehicle brand, and it got extra column that store extraneous data from the source website that I crawled.

This is the extra JSON data structure:

{
  "value": "proton",
  "data_id": 2
}

I want to query for data_id where value is ‘proton’. Previously, we’ll use LIKE condition to achieve this:

SELECT * 
FROM vehicles 
WHERE extra LIKE '%"value":"proton"%'

Now we can query for specific field inside the JSON document & return only specific data required:

SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, '$.data_id')) 
FROM vehicles 
WHERE JSON_EXTRACT(extra, '$.value') = 'proton'

It’s clearer what the query want to achieve and reduce wrong result returned by the LIKE statement.

P.s: I tried to use SELECT extra->>'$.data_id' statement, but MariaDB 11.4 return syntax error. Not sure yet whether MariaDB didn’t support, or my SQL is wrong.

Reference & more examples – https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql

#webdev