Skip to main content

JSON

With MySQL, you can:

  • Store JSON documents inside a column.
  • Validate them automatically (only valid JSON is allowed).
  • Query and manipulate JSON data using built-in JSON functions.

JSON is useful when working with semi-structured or flexible data (e.g., logs, configurations, API responses, NoSQL-like use cases).

Creating a Table with JSON Column​

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    details JSON
);

The details column will store JSON objects, arrays, or values. If you try inserting invalid JSON, MySQL will throw an error.

Inserting JSON Data​

INSERT INTO products (name, details) VALUES
('Laptop', JSON_OBJECT('brand', 'Dell', 'specs', JSON_ARRAY('i7', '16GB RAM', '512GB SSD'))),
('Phone', '{"brand": "Samsung", "model": "S23", "features": ["5G", "AMOLED", "128GB"]}');
  • JSON_OBJECT() creates a JSON object.
  • JSON_ARRAY() creates a JSON array.
  • You can also insert a JSON string directly ('{"brand":"Samsung"}').

MySQL will validate the format before storing it.

Querying JSON Data​

Use -> and ->> operators:

  • -> returns JSON value.
  • ->> returns unquoted scalar value (string, number).
SELECT
    name,
    details->'$.brand' AS brand_json,
    details->>'$.brand' AS brand_text
FROM products;

Output might look like

SELECT
    name,
    details->'$.brand' AS brand_json,
    details->>'$.brand' AS brand_text
FROM products;

Searching Inside JSON​

Find products with "brand" = "Samsung":

SELECT * FROM products
WHERE JSON_EXTRACT(details, '$.brand') = 'Samsung';

or shorter:

SELECT * FROM products
WHERE details->>'$.brand' = 'Samsung';

Modifying JSON Data​

Add or Update a key

UPDATE products
SET details = JSON_SET(details, '$.warranty', '2 years')
WHERE name = 'Laptop';

Remove a key

UPDATE products
SET details = JSON_REMOVE(details, '$.features[2]')
WHERE name = 'Phone';

This removes the 3rd element from the features array

Advanced JSON Queries​

Filtering based on array contents

Find all products that have "5G" in their features:

SELECT * FROM products
WHERE JSON_CONTAINS(details->'$.features', '"5G"');

Merging JSON objects

SELECT JSON_MERGE_PRESERVE(
    '{"brand": "Dell"}',
    '{"color": "Silver"}'
) AS merged;

Result:

{ "brand": "Dell", "color": "Silver" }

Indexing JSON Data for Performance​

Since JSON is stored as text internally, queries can be slow on large datasets. MySQL allows you to create generated (virtual) columns from JSON values and index them.

ALTER TABLE products
ADD brand VARCHAR(50) GENERATED ALWAYS AS (details->>'$.brand') STORED,
ADD INDEX idx_brand (brand);

Now queries like:

SELECT * FROM products WHERE brand = 'Samsung';