MySQL - JSON

MySQL - Working with the JSON Data Type

Since version 5.7.8, MySQL has provided native support for the JSON (JavaScript Object Notation) data type. This was a significant enhancement, allowing developers to store and query schema-less or semi-structured data directly within their relational database. Before this, developers often stored JSON as plain text, which was inefficient and lacked validation.

Advantages of the Native JSON Type

• Automatic Validation: MySQL automatically validates any document being inserted or updated into a JSON column. If the document is not valid JSON, the operation fails, ensuring data integrity.
• Optimized Storage Format: JSON documents are stored in an optimized binary format, not as raw strings. This allows for fast, direct access to individual elements within the document by key or array index, without needing to parse the entire string.
• Rich Function Set: MySQL provides a comprehensive set of functions and operators to create, manipulate, and search JSON data.

Creating Tables with JSON Columns

You define a column with the JSON data type just like any other type in a CREATE TABLE statement. A JSON column can store two types of structures: a JSON object (a collection of key-value pairs enclosed in {}) or a JSON array (an ordered list of values enclosed in []).

CREATE TABLE PRODUCTS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    -- Stores variable product attributes like color, size, material, etc.
    ATTRIBUTES JSON
);

-- Inserting data with JSON objects
INSERT INTO PRODUCTS (NAME, ATTRIBUTES) VALUES
('Laptop',   '{"brand": "ACME", "ram_gb": 16, "storage_gb": 512, "touchscreen": false}'),
('T-Shirt',  '{"brand": "Modern Apparel", "color": "blue", "size": "L", "materials": ["cotton", "polyester"]}'),
('Book',     '{"title": "The Art of SQL", "author": "A. Codd", "pages": 450}');

Querying JSON Data

MySQL offers convenient column path operators (-> and ->>) to extract data from JSON columns. This is a shortcut for the JSON_EXTRACT() function.

• ->: Extracts a value and returns it as a JSON-quoted value (e.g., "blue", 16, false).
• ->>: Extracts a value and returns it as an unquoted string (e.g., blue, 16, false). This is often more useful for display or comparison.

Example: Extracting Values

-- Extract the brand and color for all products
-- Note how some products don't have a 'color', resulting in NULL
SELECT
    NAME,
    ATTRIBUTES->>'$.brand' AS Brand,
    ATTRIBUTES->>'$.color' AS Color
FROM PRODUCTS;

NAME	Brand	Color
Laptop	ACME	NULL
T-Shirt	Modern Apparel	blue
Book	NULL	NULL

Example: Filtering with WHERE

You can use these operators directly in the WHERE clause to filter rows based on JSON content.

-- Find all products with more than 8GB of RAM
SELECT NAME, ATTRIBUTES->>'$.ram_gb' AS RAM
FROM PRODUCTS
WHERE ATTRIBUTES->>'$.ram_gb' > 8;

Advanced JSON Functions

Searching in Arrays: JSON_CONTAINS

The JSON_CONTAINS() function checks if a given value exists within a JSON document, and is particularly useful for searching within arrays.

-- Find all T-Shirts made of cotton
SELECT NAME
FROM PRODUCTS
WHERE NAME = 'T-Shirt' AND JSON_CONTAINS(ATTRIBUTES, '"cotton"', '$.materials');

Modifying JSON Documents: JSON_SET, JSON_INSERT, JSON_REPLACE

These functions allow you to modify JSON documents in-place.

• JSON_SET: Updates existing values and inserts new ones.
• JSON_INSERT: Inserts new values only; does not overwrite existing ones.
• JSON_REPLACE: Replaces existing values only; does not insert new ones.

-- Update the laptop's RAM to 32GB and add a 'backlit_keyboard' attribute
UPDATE PRODUCTS
SET ATTRIBUTES = JSON_SET(
    ATTRIBUTES,
    '$.ram_gb', 32,
    '$.backlit_keyboard', true
)
WHERE ID = 1;

Transforming JSON to Rows: JSON_TABLE() (MySQL 8.0+)

One of the most powerful features in modern MySQL is JSON_TABLE(), which lets you project JSON data into a relational, tabular format within a query. This is excellent for reporting or joining JSON data with other tables.

-- Create a virtual table of all materials from all T-Shirts
SELECT
    p.NAME,
    m.material
FROM
    PRODUCTS p,
    JSON_TABLE(
        p.ATTRIBUTES,
        '$.materials[*]'
        COLUMNS (material VARCHAR(50) PATH '$')
    ) AS m
WHERE p.NAME = 'T-Shirt';

Indexing JSON Columns for Performance

Querying large JSON columns can be slow. To optimize performance, you can create an index on a value extracted from the JSON. This is done by creating a virtual generated column and then indexing it.

-- Add a virtual column for the brand
ALTER TABLE PRODUCTS
ADD COLUMN brand_virtual VARCHAR(100) AS (ATTRIBUTES->>'$.brand') VIRTUAL;

-- Create an index on the new virtual column
CREATE INDEX idx_brand ON PRODUCTS (brand_virtual);

Now, queries like WHERE ATTRIBUTES->>'$.brand' = 'ACME' will be able to use the idx_brand index, making them significantly faster.

JSON in a Client Program

When working with JSON in an application, your database driver can often handle serialization and deserialization automatically. In Python, for example, you can pass a dictionary directly as a parameter, and fetch JSON data which is then parsed into a Python object.

Example: Python with mysql-connector

Python




import mysql.connector
import json

# --- Configuration ---
config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS',
}

# --- Data to Insert ---
new_product_name = 'Smartwatch'
new_product_attrs = {
    "brand": "TechGear",
    "display_type": "OLED",
    "features": ["GPS", "Heart Rate Monitor"],
    "water_resistant": True
}

# --- Database Interaction ---
try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor(dictionary=True) as cursor:
            print("Connection established.")

            # Insert a new product with JSON data
            # The connector can handle the dictionary, but explicit json.dumps is safer
            insert_query = "INSERT INTO PRODUCTS (NAME, ATTRIBUTES) VALUES (%s, %s)"
            cursor.execute(insert_query, (new_product_name, json.dumps(new_product_attrs)))
            connection.commit()
            product_id = cursor.lastrowid
            print(f"Inserted new product '{new_product_name}' with ID: {product_id}")

            # Fetch and display the new product's attributes
            select_query = "SELECT ATTRIBUTES FROM PRODUCTS WHERE ID = %s"
            cursor.execute(select_query, (product_id,))
            result = cursor.fetchone()

            if result and result['ATTRIBUTES']:
                # The connector returns the JSON as a string, so we parse it
                attributes_data = json.loads(result['ATTRIBUTES'])
                print("\nFetched attributes:")
                print(f"  - Brand: {attributes_data.get('brand')}")
                print(f"  - Features: {', '.join(attributes_data.get('features', []))}")

except mysql.connector.Error as err:
    print(f"Database error: {err}")
finally:
    print("\nExecution finished.")


Output
The expected output:

Connection established.
Inserted new product 'Smartwatch' with ID: 4

Fetched attributes:
  - Brand: TechGear
  - Features: GPS, Heart Rate Monitor

Execution finished.