How To Store and Query JSON Data in MySQL

February 18, 2024

MySQL has support for JSON data types, allowing you to store, index, and query JSON data efficiently. Here’s a brief overview of how you can store JSON data in MySQL:

JSON Data Type: MySQL introduced the JSON data type to store JSON data efficiently. You can define a column with the JSON data type to store JSON documents.
```
CREATE TABLE my_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   json_data JSON
);
```
Inserting JSON Data: You can insert JSON data into a column of the JSON data type directly.
```
INSERT INTO my_table (json_data) VALUES ('{"name": "John", "age": 30}');
```

Querying JSON Data: MySQL provides various functions and operators to query JSON data.

-- Select rows where the JSON document contains a specific field
SELECT * FROM my_table WHERE json_data->'$.name' = 'John';

-- Select rows where a specific field in the JSON document satisfies a condition
SELECT * FROM my_table WHERE json_data->'$.age' > 25;

-- Extract a specific field from the JSON document
SELECT json_data->'$.name' AS name FROM my_table;

Indexing JSON Data: MySQL supports indexing JSON columns, which can improve the performance of queries involving JSON data.
```
CREATE INDEX json_name_index ON my_table ((json_data->'$.name'));
```
Updating JSON Data: You can update JSON data using the JSON_SET() function or other JSON manipulation functions.
```
UPDATE my_table SET json_data = JSON_SET(json_data, '$.age', 31) WHERE id = 1;
```
Deleting JSON Data: Similarly, you can delete elements from JSON data using the JSON_REMOVE() function.
```
UPDATE my_table SET json_data = JSON_REMOVE(json_data, '$.age') WHERE id = 1;
```

Using the JSON data type in MySQL allows you to work with semi-structured data efficiently, providing flexibility in your schema design and queries.