JSON data type in MySQL

Introduction

The JSON data type lets you store and work with data in a flexible, easy-to-read format inside your database.

When you want to save a list of items that can change size, like tags on a blog post.

When you need to store settings or preferences that vary for each user.

When your data has a structure that doesn't fit neatly into fixed columns.

When you want to quickly save and retrieve complex data without creating many tables.

Syntax

MySQL

CREATE TABLE table_name (
  id INT PRIMARY KEY,
  data JSON
);

The JSON column stores data in JSON format, which is text but validated by MySQL.

You can insert JSON data using strings like '{"key": "value"}'.

Examples

Create a table with a JSON column named 'info' to store product details.

MySQL

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

Insert a product with name and price stored as JSON.

MySQL

INSERT INTO products (id, info) VALUES (1, '{"name": "Pen", "price": 1.5}');

Retrieve the 'name' value from the JSON data for product with id 1.

MySQL

SELECT info->>'$.name' AS product_name FROM products WHERE id = 1;

Sample Program

This creates a users table with a JSON column 'profile'. It inserts two users with their details in JSON. Then it selects each user's id, name, and age from the JSON data.

MySQL

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

INSERT INTO users (id, profile) VALUES
(1, '{"name": "Alice", "age": 30, "hobbies": ["reading", "hiking"]}'),
(2, '{"name": "Bob", "age": 25, "hobbies": ["gaming", "cooking"]}');

SELECT id, profile->>'$.name' AS name, profile->>'$.age' AS age FROM users;

Important Notes

MySQL validates JSON data when you insert it, so invalid JSON will cause an error.

You can use JSON functions to search, modify, and extract data inside JSON columns.

Storing data as JSON is flexible but may be slower than normal columns for some queries.

Summary

JSON data type stores flexible, structured data inside a table column.

It is useful when data doesn't fit fixed columns or changes often.

You can insert, query, and manipulate JSON data using special JSON functions.