How to Query a JSON Column

MySQL has supported JSON data types since version 5.7, allowing you to store JSON data directly in your database and then query it efficiently. In this tutorial, we'll explore how to query and manipulate JSON data stored in MySQL columns using various JSON functions.

1. Storing JSON Data in MySQL

To store JSON data in a MySQL database, you need to define a column with the JSON data type. Here's an example of creating a table with a JSON column:

CREATE TABLE user_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    details JSON
);

2. Inserting JSON Data

Once the table is created, you can insert JSON data into the column. Ensure that the data is properly formatted as a JSON object. Here's an example of how to insert JSON data into the details column:

INSERT INTO user_data (details)
VALUES ('{"name": "John", "age": 30, "city": "New York"}');

3. Querying JSON Data

To query data from a JSON column, you can use the JSON_EXTRACT() function. This function retrieves the value of a specific key from the JSON document. For example:

SELECT JSON_EXTRACT(details, '$.name') AS name
FROM user_data;

This query will return the value of the name key from the JSON document stored in the details column.

4. Filtering JSON Data

You can also filter rows based on the values inside the JSON column. For example, to find all users whose city is "New York," use the following query:

SELECT *
FROM user_data
WHERE JSON_EXTRACT(details, '$.city') = 'New York';

5. Updating JSON Data

To update a value within a JSON document, you can use the JSON_SET() function. For example, to update the age of a user:

UPDATE user_data
SET details = JSON_SET(details, '$.age', 31)
WHERE JSON_EXTRACT(details, '$.name') = 'John';

6. Using JSON_ARRAY() and JSON_OBJECT()

You can also construct JSON data using MySQL's JSON_ARRAY() and JSON_OBJECT() functions. These functions allow you to build JSON arrays or objects from individual values. For example:

SELECT JSON_OBJECT('name', 'John', 'age', 31, 'city', 'New York') AS new_details;

This query returns a JSON object with the provided key-value pairs.

7. Conclusion

MySQL provides a rich set of functions for querying and manipulating JSON data, making it easier to work with semi-structured data in your relational database. With the examples provided, you should now be able to query, filter, and update JSON data efficiently.

If you have any questions or want to explore more advanced features, feel free to leave a comment below!