The JSON Type — MySQL

MySQL JSON Type

MySQL 5.7.8 introduced the native JSON column type, and MySQL 8.0 added significant improvements including partial updates and multi-valued indexes. The JSON type stores validated JSON documents in a binary format that is more efficient than TEXT — MySQL can access specific paths without parsing the entire document.

JSON columns bring document-store flexibility to a relational database, letting you handle semi-structured or schema-flexible data alongside your normalized tables.

Declaring a JSON Column

SQL

CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10, 2) NOT NULL,
  -- JSON column for flexible attributes
  attributes  JSON,
  metadata    JSON NOT NULL DEFAULT (JSON_OBJECT())
);

Note

JSON columns cannot have a traditional DEFAULT value as a string literal. Use DEFAULT (JSON_OBJECT()) or DEFAULT (JSON_ARRAY()) (note the parentheses) for a dynamic default expression. JSON columns also cannot be directly indexed — use generated columns for that.

Inserting JSON Data

SQL

-- Insert a raw JSON string (MySQL validates the JSON)
INSERT INTO products (name, price, attributes) VALUES
  ('Laptop Pro', 1299.99, '{"brand":"TechCorp","ram_gb":16,"storage_gb":512,"color":"silver"}'),
  ('Wireless Mouse', 29.99, '{"brand":"ClickMaster","wireless":true,"dpi":1600}');

-- Build JSON using JSON_OBJECT()
INSERT INTO products (name, price, attributes) VALUES
  ('Mechanical Keyboard', 89.99,
   JSON_OBJECT(
     'brand', 'TypeFast',
     'switch_type', 'Cherry MX Blue',
     'backlighting', true,
     'keys', JSON_ARRAY('US', 'DE', 'FR')
   )
  );

-- Invalid JSON raises an error immediately
INSERT INTO products (name, price, attributes) VALUES
  ('Test', 9.99, '{invalid json}');
-- Error 3140: Invalid JSON text

Extracting Data: -> and ->>

MySQL provides two shorthand operators for JSON path extraction:

Operator	Function Equivalent	Returns
col->'$.path'	JSON_EXTRACT(col, '$.path')	JSON value (strings are double-quoted)
col->>'$.path'	JSON_UNQUOTE(JSON_EXTRACT(col, '$.path'))	Unquoted string (plain text)

SQL

-- -> returns a JSON value (strings are quoted)
SELECT attributes->'$.brand' FROM products WHERE id = 1;
-- Result: "TechCorp"  (note the quotes)

-- ->> returns unquoted string
SELECT attributes->>'$.brand' FROM products WHERE id = 1;
-- Result: TechCorp  (no quotes)

-- Access nested paths
SELECT
  name,
  attributes->>'$.brand' AS brand,
  attributes->>'$.ram_gb' AS ram,
  attributes->>'$.storage_gb' AS storage
FROM products
WHERE attributes->'$.ram_gb' >= 16;

+------------+-----------+-----+---------+
| name       | brand     | ram | storage |
+------------+-----------+-----+---------+
| Laptop Pro | TechCorp  | 16  | 512     |
+------------+-----------+-----+---------+

JSON_EXTRACT with Arrays

SQL

-- Array element access by index (0-based)
SELECT attributes->'$.keys[0]' FROM products WHERE id = 3;
-- Result: "US"

-- Array length
SELECT JSON_LENGTH(attributes->'$.keys') FROM products WHERE id = 3;
-- Result: 3

-- All array elements
SELECT JSON_EXTRACT(attributes, '$.keys') FROM products WHERE id = 3;
-- Result: ["US", "DE", "FR"]

-- Wildcard: all values at a path level
SELECT JSON_EXTRACT('{"a":1,"b":2,"c":3}', '$.*');
-- Result: [1, 2, 3]

Modifying JSON: JSON_SET, INSERT, REPLACE, REMOVE

SQL

-- JSON_SET: insert or update a path (creates the path if it doesn't exist)
UPDATE products
SET attributes = JSON_SET(attributes, '$.color', 'black')
WHERE id = 1;

-- Update multiple paths at once
UPDATE products
SET attributes = JSON_SET(
  attributes,
  '$.ram_gb',   32,
  '$.upgraded', true
)
WHERE id = 1;

-- JSON_INSERT: insert only — does NOT overwrite existing values
UPDATE products
SET attributes = JSON_INSERT(attributes, '$.warranty_years', 2)
WHERE id = 1;

-- JSON_REPLACE: update only — does NOT create new paths
UPDATE products
SET attributes = JSON_REPLACE(attributes, '$.ram_gb', 64)
WHERE id = 1;

-- JSON_REMOVE: delete a path
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.color')
WHERE id = 1;

-- Remove multiple paths
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.upgraded', '$.warranty_years')
WHERE id = 1;

Tip

Use JSON_SET for upsert behavior (create or update), JSON_INSERT when you only want to add new keys without overwriting, and JSON_REPLACE when you only want to update existing keys. Combining them precisely avoids accidental data loss.

Searching JSON: JSON_CONTAINS and JSON_SEARCH

SQL

-- JSON_CONTAINS: check if a value exists at a path
SELECT name FROM products
WHERE JSON_CONTAINS(attributes, '"TechCorp"', '$.brand');

-- Check if a JSON object contains a key-value pair
SELECT name FROM products
WHERE JSON_CONTAINS(attributes, '{"wireless": true}');

-- Check if array contains a specific value
SELECT name FROM products
WHERE JSON_CONTAINS(attributes->'$.keys', '"DE"');

-- JSON_SEARCH: find the path to a value
SELECT JSON_SEARCH(attributes, 'one', 'TechCorp') FROM products WHERE id = 1;
-- Result: "$.brand"  (the path where 'TechCorp' was found)

-- Search for a value in all paths ('all' vs 'one')
SELECT JSON_SEARCH('["a","b",["c","a"]]', 'all', 'a');
-- Result: ["$[0]", "$[2][1]"]

Other Useful JSON Functions

SQL

-- JSON_TYPE: return the JSON type of a value
SELECT JSON_TYPE(attributes) FROM products LIMIT 1;
-- Result: OBJECT

SELECT JSON_TYPE(attributes->'$.ram_gb') FROM products WHERE id = 1;
-- Result: INTEGER

-- JSON_KEYS: return an array of top-level keys
SELECT JSON_KEYS(attributes) FROM products WHERE id = 1;
-- Result: ["brand", "ram_gb", "storage_gb", "color"]

-- JSON_LENGTH: length of array or object
SELECT JSON_LENGTH(attributes) FROM products WHERE id = 1;
-- Result: 4 (number of keys)

-- JSON_DEPTH: deepest nesting level
SELECT JSON_DEPTH('{"a":{"b":{"c":1}}}');
-- Result: 4

-- JSON_VALID: check if a string is valid JSON
SELECT JSON_VALID('{"key": "value"}');  -- 1
SELECT JSON_VALID('{invalid}');         -- 0

-- JSON_MERGE_PATCH: merge two JSON objects (second wins on conflicts)
SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":3,"c":4}');
-- Result: {"a": 1, "b": 3, "c": 4}

-- JSON_PRETTY: format JSON with indentation
SELECT JSON_PRETTY(attributes) FROM products WHERE id = 1;

Generated Columns for JSON Indexing

JSON columns themselves cannot be directly indexed. To index a JSON path, create a generated column that extracts the value, then index the generated column.

SQL

ALTER TABLE products
  -- Add a virtual generated column that extracts the brand
  ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL,

  -- Add an index on the generated column
  ADD INDEX idx_brand (brand);

-- Now queries on brand use the index efficiently
SELECT name, price FROM products WHERE brand = 'TechCorp';
-- Uses idx_brand index!

-- STORED generated column (computed and stored on disk, faster to read)
ALTER TABLE products
  ADD COLUMN ram_gb SMALLINT UNSIGNED
    GENERATED ALWAYS AS (CAST(attributes->>'$.ram_gb' AS UNSIGNED)) STORED,
  ADD INDEX idx_ram (ram_gb);

Note

VIRTUAL generated columns are computed on read (no extra storage, but CPU cost per read). STORED generated columns are computed on insert/update and stored on disk (more storage, faster reads). Index a STORED column for frequently filtered JSON paths.

Multi-Valued Indexes (MySQL 8.0.17+)

MySQL 8.0.17 introduced multi-valued indexes, which index all values in a JSON array at once — allowing efficient queries like "find products with 'DE' in their keys array."

SQL

-- Create a multi-valued index on a JSON array
CREATE TABLE products2 (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  attributes JSON,
  -- Multi-valued index on the 'keys' array
  INDEX idx_keys ((CAST(attributes->'$.keys' AS CHAR(10) ARRAY)))
);

-- This query efficiently uses the multi-valued index
SELECT name FROM products2
WHERE 'DE' MEMBER OF (attributes->'$.keys');

-- Alternative syntax with JSON_OVERLAPS (any overlap between two arrays)
SELECT name FROM products2
WHERE JSON_OVERLAPS(attributes->'$.keys', '["DE","FR"]');

JSON Performance Best Practices

Index what you filter on: Any JSON path used in WHERE clauses should have a generated column with an index. Unindexed JSON path queries do full table scans.
Keep JSON documents small: Large JSON documents (over a few KB) slow down row fetches because the entire document loads even if you only need one field. Consider splitting large JSON into separate normalized columns.
Don't abuse JSON for relational data: JSON is for truly flexible or variable-attribute data. If every product has the same attributes (all have price, name, sku), put them in regular columns. JSON shines for optional attributes that vary by product type.
Use JSON_TABLE for complex queries: The JSON_TABLE() function (MySQL 8.0+) converts JSON arrays into a relational table, enabling powerful JOIN-like operations on array data.
Partial updates are efficient: MySQL 8.0 can update individual JSON paths without rewriting the entire document (binary diff), making JSON_SET on large documents more efficient.

JSON_TABLE: Relational Access to JSON Arrays

SQL

-- JSON_TABLE converts a JSON array into rows you can query
-- Example: expand the 'keys' array into individual rows
SELECT p.name, k.locale
FROM products p,
  JSON_TABLE(
    p.attributes,
    '$.keys[*]'
    COLUMNS (
      locale CHAR(2) PATH '$'
    )
  ) AS k
WHERE p.id = 3;

+---------------------+--------+
| name                | locale |
+---------------------+--------+
| Mechanical Keyboard | US     |
| Mechanical Keyboard | DE     |
| Mechanical Keyboard | FR     |
+---------------------+--------+

SQL

-- JSON_TABLE with a complex nested structure
-- Given: orders with a JSON array of line items
CREATE TABLE json_orders (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_data JSON NOT NULL
);

INSERT INTO json_orders (order_data) VALUES
  ('{"customer":"Alice","items":[{"sku":"A1","qty":2,"price":9.99},{"sku":"B2","qty":1,"price":24.99}]}');

SELECT
  jo.id,
  jo.order_data->>'$.customer' AS customer,
  items.sku,
  items.qty,
  items.price,
  items.qty * items.price AS line_total
FROM json_orders jo,
  JSON_TABLE(
    jo.order_data,
    '$.items[*]'
    COLUMNS (
      sku   VARCHAR(20) PATH '$.sku',
      qty   INT         PATH '$.qty',
      price DECIMAL(8,2) PATH '$.price'
    )
  ) AS items;

Warning

JSON_TABLE is powerful but has no index support — it always scans and parses the JSON document. Use it for reporting and occasional complex queries, not in hot query paths. For frequently-accessed nested data, consider normalizing into relational tables.