How to Store JSON in MySQL: Syntax and Examples

In MySQL, you can store JSON data using the JSON data type, which allows you to save valid JSON documents efficiently. Use CREATE TABLE with a column of type JSON and insert JSON strings directly into that column.

Syntax

To store JSON in MySQL, define a column with the JSON data type. This ensures the data is validated as proper JSON and stored efficiently.

Example syntax parts:

• CREATE TABLE: creates a new table.
• json_column JSON: declares a column named json_column with JSON type.
• INSERT INTO: inserts JSON data as a string into the JSON column.

sql

CREATE TABLE example_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  json_data JSON
);

Example

This example shows how to create a table with a JSON column, insert JSON data, and select it back.

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  info JSON
);

INSERT INTO users (info) VALUES
('{"name": "Alice", "age": 30}'),
('{"name": "Bob", "age": 25}');

SELECT * FROM users;

Output

+----+-----------------------------+
| id | info                        |
+----+-----------------------------+
|  1 | {"name": "Alice", "age": 30} |
|  2 | {"name": "Bob", "age": 25}   |
+----+-----------------------------+

Common Pitfalls

Common mistakes when storing JSON in MySQL include:

• Storing JSON as TEXT without validation, which can lead to invalid JSON data.
• Inserting malformed JSON strings that cause errors.
• Not using MySQL JSON functions to query or manipulate JSON data efficiently.

Always use the JSON data type to ensure data integrity and better performance.

sql

/* Wrong: storing JSON as TEXT without validation */
CREATE TABLE wrong_table (
  data TEXT
);

INSERT INTO wrong_table (data) VALUES ('{name: "Alice", age: 30}'); -- Missing quotes around keys, invalid JSON

/* Right: using JSON data type with valid JSON */
CREATE TABLE right_table (
  data JSON
);

INSERT INTO right_table (data) VALUES ('{"name": "Alice", "age": 30}');

Quick Reference

Summary tips for storing JSON in MySQL:

Tip	Description
Use JSON data type	Ensures data is valid JSON and stored efficiently.
Insert valid JSON strings	Always use properly formatted JSON with quotes around keys and strings.
Use JSON functions	Leverage MySQL JSON functions like JSON_EXTRACT for querying.
Avoid TEXT for JSON	TEXT columns do not validate JSON and can cause errors later.
Index JSON columns	Use generated columns and indexes for faster queries on JSON data.

Key Takeaways

Use the JSON data type in MySQL to store JSON data safely and efficiently.

Always insert properly formatted JSON strings with quotes around keys and values.

Avoid storing JSON as plain TEXT to prevent invalid data and improve performance.

Use MySQL JSON functions to query and manipulate JSON data effectively.

Consider indexing JSON data using generated columns for faster searches.