JSON Data Type in MySQL

MySQL has supported native JSON data type since version 5.7.8, allowing developers to store and query JSON documents directly within relational database tables. This feature bridges the gap between traditional relational databases and NoSQL document stores.

Creating Tables with JSON Columns

To create a table with a JSON column, use the following syntax:

CREATE TABLE user_profiles (
    user_id INT AUTO_INCREMENT,
    profile_data JSON,
    PRIMARY KEY(user_id)
);

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT, 
    attributes JSON,
    PRIMARY KEY(product_id)
);

Inserting JSON Data

Inserting JSON data requires proper string formatting with single quotes enclosing the JSON document:

INSERT INTO user_profiles VALUES (
    NULL, '{"name":"alex","age":25,"city":"seattle"}' 
);

INSERT INTO user_profiles VALUES (
    NULL, '{"name":"sarah","age":32,"email":"sarah@example.com"}'
);

Inserting a more complex JSON document with nested arrays:

INSERT INTO products(attributes) 
VALUES('{"category":"electronics","features":["wifi","bluetooth","gps"]}');

Modifying JSON Data

To modify specific elements within a JSON document, use MySQL's JSON functions. For example, to add an element to an array:

UPDATE products SET attributes=JSON_ARRAY_APPEND(attributes,'$.features','nfc') WHERE product_id = 1;

JSON Functions

JSON_EXTRACT

Extract specific values from a JSON document:

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');

Extracting name and city from user profiles:

SELECT
    JSON_EXTRACT(profile_data, '$.name'),
    JSON_EXTRACT(profile_data, '$.city')
FROM user_profiles;

JSON_OBJECT

Create JSON objects from key-value pairs:

SELECT JSON_OBJECT("name", "mike", "email", "mike@example.com", "age", 42);

INSERT INTO user_profiles VALUES (
    NULL, JSON_OBJECT("name", "david", "email", "david@example.com", "age", 28)
);

JSON_INSERT

Insert values into a JSON document without replacing existing values:

SET @json_doc = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]');

Adding a new field to existing JSON data:

UPDATE user_profiles SET profile_data = JSON_INSERT(profile_data, "$.country", "usa") WHERE user_id = 1;

JSON_MERGE_PRESERVE

Combine multiple JSON documents:

SELECT JSON_MERGE_PRESERVE('{"name": "john"}', '{"id": 47}');

SELECT
    JSON_MERGE_PRESERVE(
        JSON_EXTRACT(profile_data, '$.city'),
        JSON_EXTRACT(profile_data, '$.country')
    )
FROM user_profiles WHERE user_id = 1;

JSON Indexing

JSON columns cannot be indexed directly. Instead, create generated columns that extract the values you want to index:

CREATE TABLE user_index (
    profile_data JSON,
    user_name VARCHAR(50) GENERATED ALWAYS AS (JSON_EXTRACT(profile_data, '$.name')),
    INDEX name_idx (user_name)
);

For better performanec, use JSON_UNQUOTE to remove quotes from string values:

CREATE TABLE user_optimized (
    profile_data JSON,
    user_name VARCHAR(50) GENERATED ALWAYS AS (
        JSON_UNQUOTE(JSON_EXTRACT(profile_data, "$.name"))
    ),
    KEY name_index(user_name)
);

INSERT INTO user_optimized(profile_data) VALUES ('{"name":"alice", "age":35, "city":"boston"}');
INSERT INTO user_optimized(profile_data) VALUES ('{"name":"bob", "age":42, "city":"chicago"}');

SELECT JSON_EXTRACT(profile_data,"$.name") AS username FROM user_optimized WHERE user_name="alice";

Creating Indexes on JSON Data

Here's a complete example of creating and using an index on JSON data:

CREATE TABLE employees (
    emp_data JSON,
    dept_id INT GENERATED ALWAYS AS (emp_data->"$.department"),
    INDEX dept_index (dept_id)
);

INSERT INTO employees (emp_data) VALUES
    ('{"id": "1", "name": "Alice", "department": 3}'), 
    ('{"id": "2", "name": "Bob", "department": 2}'),
    ('{"id": "3", "name": "Charlie", "department": 3}'), 
    ('{"id": "4", "name": "Diana", "department": 1}');

SELECT emp_data->>"$.name" AS name
FROM employees WHERE dept_id = 3;