How to search or query data inside json column in MySQL database

How to search or query data inside json column in MySQL database

Like we know, at this moment MySQL support to save json data in table column like a PostgreSQL.
But some people don't know how to search or query data inside json column.

In this post I'll share how to search or query json data in MySQL or MySQLi database.

The first thing you should know, this method can only be done on the MySQL version >= 5.7
If you use MySQL version older than 5.7 I think this method not gonna works.

Here the steps to search or query json data in MySQL or MySQLi database:
I've table with data like below;

Then I will search or query for vehicles data that has more than 4 tires.
Here is the sample query;

SELECT * FROM vehicles
WHERE JSON_EXTRACT(specifications, "$.tires") > 4

Execute the query.
Walaaaa.... the vehicles data is filtered.

You can also display certain data that is in the json column.
Here the sample query if I only want to show tires data;

SELECT JSON_EXTRACT(specifications, "$.tires") AS tires
FROM vehicles
WHERE JSON_EXTRACT(specifications, "$.tires") = 6

Query is so simple like that,