How to Query JSON Objects in MySQL
We can search JSON objects in our MySQL tables using either JSON_EXTRACT() or the arrow operator -> to parse the JSON string.
Suppose we have a table products with columns id and json_data.
| id | json_data |
|---|---|
0 |
{"num": 1} |
1 |
{"num": 2} |
2 |
{"num": 3} |
1. Using JSON_EXTRACT()
If we have MySQL version >= 5.7, we can use JSON_EXTRACT to target a certain field in our JSON string.
SELECT JSON_EXTRACT(json_data, "$.num") FROM products
We can also append a WHERE clause to this statement that depends on a JSON value.
SELECT JSON_EXTRACT(json_data, "$.num") FROM products
WHERE JSON_EXTRACT(json_data, "$.num") < 2
2. Using the arrow operator ->
In version 5.7.9, the -> operator became an alias for JSON_EXTRACT().
SELECT json_data->"$.num" FROM products
Similarly, we can use the arrow operator in our WHERE clauses.
SELECT json_data->"$.num" FROM products
WHERE json_data->"$.num" < 2