How to Query JSON Objects in MySQL

Published Jul 22, 2022  ∙  Updated Jul 23, 2022

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