JSON in MySQL

Not all data fits neatly into fixed columns. A mouse has a DPI rating and a battery type; a monitor has a screen size and a number of HDMI ports; headphones have battery hours. Adding a column for every possible feature of every product would give a table with hundreds of mostly empty columns.

MySQL's JSON type lets one column hold a small structured document with its own keys. In this lesson you will store JSON, read values out of it with paths and the -> and ->> operators, build and change JSON in SQL, index a JSON value, and learn when JSON is the right tool and when ordinary columns are better.

The JSON column type

JSON (JavaScript Object Notation) writes data as {"key": value} pairs. Values can be text in double quotes, numbers, true, false, null, arrays in square brackets, or more objects. In MySQL you declare a column with the type JSON:

CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY,
  specs JSON NOT NULL
);

INSERT INTO product_specs (product_id, specs) VALUES
  (1, '{"colour": "black", "dpi": 1600, "battery": "AA"}'),
  (3, '{"colour": "silver", "size_inch": 27, "ports": {"hdmi": 2, "usb_c": 1}}'),
  (6, '{"colour": "blue", "battery_hours": 30, "tags": ["wireless", "anc"]}');

SELECT p.product_name,
       s.specs ->> '$.colour' AS colour,
       s.specs ->> '$.ports.hdmi' AS hdmi_ports,
       s.specs ->> '$.tags[0]' AS first_tag
FROM product_specs s
JOIN products p ON p.product_id = s.product_id
ORDER BY p.product_id;

product_name                | colour | hdmi_ports | first_tag
----------------------------+--------+------------+----------
Wireless Mouse              | black  | NULL       | NULL
27-inch Monitor             | silver | 2          | NULL
Noise-Cancelling Headphones | blue   | NULL       | wireless
(3 rows)

Each product has different keys, and that is fine. A key that does not exist simply gives NULL, as the mouse shows for hdmi_ports.

A JSON column in MySQL is more than text:

• MySQL validates every value. Inserting broken JSON, such as a missing quote, fails with an "Invalid JSON text" error, so bad documents never get in. (The browser's SQLite stores it as plain text and does not check.)
• MySQL stores JSON in a binary format that lets it jump straight to a key without reading the whole document.

JSON paths

To reach a value inside a document you write a path, a short string that starts with $, which means "the whole document":

Array positions start at 0 in JSON paths, unlike SUBSTR() in SQL, which starts at 1.

The -> and ->> operators

MySQL has two short operators for reading a value from a JSON column:

• column -> 'path' returns the value as JSON. Text keeps its double quotes.
• column ->> 'path' returns the value unquoted, as plain text. This is what you want for display and comparisons.

CREATE TABLE product_specs (product_id INTEGER PRIMARY KEY, specs JSON NOT NULL);
INSERT INTO product_specs (product_id, specs) VALUES
  (1, '{"colour": "black", "dpi": 1600, "battery": "AA"}'),
  (3, '{"colour": "silver", "size_inch": 27, "ports": {"hdmi": 2, "usb_c": 1}}'),
  (6, '{"colour": "blue", "battery_hours": 30, "tags": ["wireless", "anc"]}');

SELECT product_id,
       specs -> '$.colour' AS with_arrow,
       specs ->> '$.colour' AS with_double_arrow
FROM product_specs
WHERE specs ->> '$.colour' <> 'black'
ORDER BY product_id;

product_id | with_arrow | with_double_arrow
-----------+------------+------------------
3          | "silver"   | silver
6          | "blue"     | blue
(2 rows)

Use ->> in WHERE too. Comparing specs -> '$.colour' with 'black' compares a JSON value with text, which is easy to get wrong.

These operators are shortcuts for functions you will also see in MySQL code. -> is JSON_EXTRACT(), and ->> is JSON_UNQUOTE(JSON_EXTRACT()). In MySQL, JSON_EXTRACT keeps the quotes:

SELECT product_id,
       JSON_EXTRACT(specs, '$.colour') AS extracted,
       JSON_UNQUOTE(JSON_EXTRACT(specs, '$.colour')) AS unquoted
FROM product_specs
ORDER BY product_id;

product_id | extracted | unquoted
-----------+-----------+---------
1          | "black"   | black
3          | "silver"  | silver
6          | "blue"    | blue
(3 rows)

The browser's JSON_EXTRACT behaves differently (it returns unquoted text), which is one more reason to prefer ->>: it gives the same answer everywhere.

Building and changing JSON

JSON_OBJECT() builds a JSON object from key and value pairs. It is handy when an API or a JavaScript front end wants JSON straight from the database:

SELECT JSON_OBJECT('city', city, 'name', first_name) AS customer_json
FROM customers
WHERE state = 'Haryana'
ORDER BY customer_id;

customer_json
---------------------------------
{"city":"Rohtak","name":"Aarav"}
{"city":"Rohtak","name":"Vikram"}
{"city":"Gurugram","name":"Isha"}
(3 rows)

MySQL writes the same objects with a space after each colon and comma, such as {"city": "Rohtak", "name": "Aarav"}, and it may store keys in a different order from the one you wrote. Key order never matters in JSON. Related MySQL functions are JSON_ARRAY() to build an array and JSON_ARRAYAGG() to collect a column from many rows into one array.

To change part of a document without rewriting it, use JSON_SET(). It sets one or more paths, adding keys that do not exist yet:

CREATE TABLE product_specs (product_id INTEGER PRIMARY KEY, specs JSON NOT NULL);
INSERT INTO product_specs (product_id, specs) VALUES
  (1, '{"colour": "black", "dpi": 1600, "battery": "AA"}');

UPDATE product_specs
SET specs = JSON_SET(specs, '$.colour', 'white', '$.dpi', 2400)
WHERE product_id = 1;

SELECT specs ->> '$.colour' AS colour, specs ->> '$.dpi' AS dpi
FROM product_specs;

colour | dpi
-------+-----
white  | 2400
(1 row)

MySQL also has JSON_INSERT() (adds keys but never overwrites), JSON_REPLACE() (overwrites but never adds) and JSON_REMOVE() (deletes a path).

Searching inside arrays

To ask "does this array contain a value?", MySQL offers JSON_CONTAINS(), and since version 8.0.17 the MEMBER OF operator:

SELECT product_id
FROM product_specs
WHERE JSON_CONTAINS(specs, '"wireless"', '$.tags');

-- the same question with MEMBER OF
SELECT product_id
FROM product_specs
WHERE 'wireless' MEMBER OF (specs -> '$.tags');

product_id
----------
6
(1 row)

Note the extra double quotes in '"wireless"': the second argument of JSON_CONTAINS must itself be JSON, and JSON text needs double quotes.

Indexing a JSON value

MySQL cannot index a whole JSON column directly. If you often search by one key, create a generated column that extracts it, and index that column:

ALTER TABLE product_specs
  ADD COLUMN colour VARCHAR(20) GENERATED ALWAYS AS (specs ->> '$.colour') VIRTUAL,
  ADD INDEX idx_specs_colour (colour);

SELECT product_id FROM product_specs WHERE colour = 'silver';

The generated column is calculated from specs automatically, so you never write to it yourself, and the index makes searches by colour fast (see Indexes & EXPLAIN).

When to use JSON

JSON is a good fit for:

• attributes that differ between rows, such as product specifications by category;
• data you store and return as a whole, such as an API response or user settings;
• optional extras you rarely search on.

Use normal columns for anything you filter, join, sort or total regularly: prices, quantities, dates, status, foreign keys. Normal columns get proper types, constraints and simple indexes, and every query is easier to write. A common healthy design is a normal table with a few core columns plus one JSON column for the extras. If you find yourself putting customer IDs or order totals inside JSON, step back and read Normalisation & Table Design.

Key points

• The JSON type stores validated JSON documents in an efficient binary format.
• Paths start with $: '$.key', '$.outer.inner', '$.list[0]' (arrays count from 0).
• -> returns a JSON value (text keeps its quotes); ->> returns plain unquoted text.
• JSON_OBJECT builds objects; JSON_SET, JSON_INSERT, JSON_REPLACE and JSON_REMOVE change them.
• Index a JSON key through a generated column.
• Use JSON for flexible extras, and normal columns for data you filter, join and calculate with.

CREATE TABLE customer_prefs (
  customer_id INTEGER PRIMARY KEY,
  prefs JSON NOT NULL
);

INSERT INTO customer_prefs (customer_id, prefs) VALUES
  (1, '{"language": "Hindi", "sms_alerts": "yes", "favourite_category": "Audio"}'),
  (2, '{"language": "English", "sms_alerts": "no", "favourite_category": "Displays"}'),
  (6, '{"language": "Malayalam", "sms_alerts": "yes", "favourite_category": "Audio"}');

SELECT c.first_name,
       p.prefs ->> '$.language' AS language,
       p.prefs ->> '$.favourite_category' AS favourite
FROM customer_prefs p
JOIN customers c ON c.customer_id = p.customer_id
WHERE p.prefs ->> '$.favourite_category' = 'Audio';