Creating and Querying a MySQL JSON Column From Scratch

A JSON column in MySQL is easy to declare and has a few habits that make the first week confusing: a default that has to be written differently, three kinds of "nothing", sorting that depends on which operator you used. This article starts from an empty table and goes through creating the column, the ways of putting data in, the queries you write most, and the small behaviors that surprise people. It is the practical companion to the guide to MySQL's JSON type.

Declaring the column

The type name is JSON. There is no length. A NOT NULL is a good idea if the document is part of the row's meaning, because it keeps you from handling both a NULL and an empty object everywhere:

create table settings (
  user_id int primary key,
  prefs   json not null default (json_object()),
  flags   json default null,
  created datetime default current_timestamp
);

The parentheses around json_object() matter. A JSON column cannot have a plain literal default such as default '{}', because the type does not allow a literal default. Since 8.0.13 an expression default in parentheses is allowed, and it is evaluated for each inserted row. With it, insert into settings (user_id) values (1) stored {} in prefs and SQL NULL in flags.

A JSON column also cannot be a primary key or have an ordinary index on its raw value; the options are in indexing a MySQL JSON field with a generated column.

Putting documents in

Four forms work. I used all of them in one script:

insert into settings (user_id, prefs) values
  (2, '{"theme":"dark","lang":"en","notify":{"email":true,"sms":false}}');
insert into settings (user_id, prefs, flags)
  values (3, json_object('theme', 'light', 'lang', 'fr'), json_array('beta', 'admin'));
insert into settings (user_id, prefs, flags)
  values (4, cast('{"theme": "dark"}' as json), 'null');
insert into settings (user_id, prefs)
  values (5, json_merge_patch('{"theme":"dark"}', '{"lang":"de"}'));

A string literal is parsed as JSON on its way in. json_object and json_array build values from SQL values and are the safest way to include data that comes from a variable, because they handle quoting for you. cast(... as json) is explicit parsing. Functions such as json_merge_patch return JSON. After these inserts the table held:

user_id  prefs                                                          flags
1        {}                                                              NULL
2        {"lang": "en", "theme": "dark", "notify": {"sms": false, "email": true}}   NULL
3        {"lang": "fr", "theme": "light"}                                ["beta", "admin"]
4        {"theme": "dark"}                                               null
5        {"lang": "de", "theme": "dark"}                                 NULL

Notice the key order, lang before theme: it is the stored order, not the order I typed. And notice row 4: I inserted the text 'null', and flags now holds the JSON value null.

Never build the text yourself by concatenating strings in application code. A user name with a quote in it produces invalid JSON or, worse, JSON with extra keys. Pass the document as a bound parameter, or build it with json_object. The failure modes are covered in fixing invalid JSON text errors in MySQL inserts.

Three kinds of nothing

A JSON column can hold SQL NULL, the JSON value null, or a document without the key you ask about. They are not the same:

select user_id, flags is null as sql_null, json_type(flags) from settings order by user_id;

user_id  sql_null  json_type(flags)
1        1         NULL
2        1         NULL
3        0         ARRAY
4        0         NULL
5        1         NULL

User 4's flags is a JSON null, so flags is null is 0, while users 1, 2 and 5 have SQL NULL and is null is 1. Yet json_type prints NULL for both, since it returns the name NULL for the JSON null and SQL NULL for a SQL NULL. They look identical in a result grid. If you need to find real JSON nulls, test flags is not null and json_type(flags) = 'NULL'. The better answer is to avoid storing JSON nulls in a column that already allows SQL NULL, or to pick one convention and enforce it with a check constraint.

For keys inside a document, a missing key and a key with a JSON null differ too, as the hub guide shows: -> on a missing path returns SQL NULL, and ->> on a JSON null returns the four-character text null. If a filter is meant to find "no theme set", prefs ->> '$.theme' is null finds only the missing key, not {"theme": null}.

Filtering

The common queries read like ordinary SQL once you pick the operator:

select user_id from settings where prefs ->> '$.theme' = 'dark';          -- 2, 4, 5
select user_id from settings where prefs ->> '$.notify.email' = 'true';   -- 2
select user_id from settings where prefs -> '$.notify.email' = true;      -- 2
select user_id from settings where json_contains_path(prefs, 'one', '$.notify');  -- 2

For text, ->> compared to a string. For booleans you can compare the JSON value to true or the text to 'true'. Existence of a key is json_contains_path. Defaults for missing keys come from coalesce:

select user_id, coalesce(prefs ->> '$.lang', 'en') as lang from settings order by 1;

Users 1, 2 and 4 got en in my run, user 2 because its own value is en, and users 1 and 4 because lang is absent. Remember that string comparisons against JSON text are case sensitive, since the result of ->> carries the binary collation, so where prefs ->> '$.theme' = 'Dark' finds nothing.

For the structure itself, json_length(prefs) counts keys, json_keys(prefs) lists them (["lang", "theme", "notify"] for user 2 and [] for the empty object), and json_type() reports the type of a value. Note that keys is a reserved word, so json_length(prefs) as keys is a syntax error; I got one when I tried the alias.

Sorting and grouping

ORDER BY on a document value needs care, because the operator decides the type:

select user_id, prefs ->> '$.lang' as lang from settings
order by prefs ->> '$.lang', user_id;

It returned users 1 and 4 first (the NULLs), then 5 (de), 2 (en) and 3 (fr): ascending order puts NULL first in MySQL. order by json_extract(prefs, '$.lang') desc sorted the JSON strings, which for plain ASCII gives the same order, with the NULLs last.

JSON values compare with their own rules when you compare them as JSON. An array compares element by element ([3, 10, 2] is less than [10]), strings compare as strings, and across types there is a fixed order, so cast('1' as json) < cast('"a"' as json), cast('true' as json) > cast('1' as json) and cast('null' as json) < cast('1' as json) were all true. You rarely need this. What you need is to sort numbers as numbers.

On the 200,000-row products table, order by attrs ->> '$.price' desc limit 3 should sort text, where 99.99 follows 9 and beats 500.0. Before I added any index, I measured that comparison with where attrs ->> '$.price' > '450' and got 43,661 rows where the numeric answer is 20,061. For the ordering, my first run, after I had created a generated price column with an index in an earlier experiment, returned 500.0, 500.0, 499.99, the numeric order, and explain showed Backward index scan on idx_price. MySQL had quietly used the index on the generated column to answer the sort. When I defeated that with order by concat(attrs ->> '$.price', ''), it sorted as text and returned 99.99 three times. The ordering of your results depended on whether an index existed. If you want numbers, say so: order by cast(attrs ->> '$.price' as decimal(8,2)), or order by attrs -> '$.price', which compares as JSON numbers.

Grouping works on the extracted value. select attrs -> '$.specs.color' c, count(*) from products group by c returned "black", "blue", "green" and "red" with their quotes, because -> returns JSON. Use ->> to group on plain text, and note that grouping on a JSON value compares JSON values, so 1 and 1.0 group together.

Upserts that merge

INSERT ... ON DUPLICATE KEY UPDATE can merge a new fragment into an existing document, using JSON_MERGE_PATCH:

insert into settings (user_id, prefs) values (6, json_object('a', 1)) as new
  on duplicate key update prefs = json_merge_patch(settings.prefs, new.prefs);

Running it twice, the second time with json_object('b', 2), left {"a": 1, "b": 2} for user 6. This is a neat way to keep preferences. It overwrites matching keys, removes keys set to null in the new fragment (the patch rule), and replaces arrays whole. If you want a key set to null to remain, use JSON_SET instead of a patch. The row alias as new needs MySQL 8.0.19 or later; before that, the form was values(prefs).

Selecting for the application

Depending on the driver, a JSON column arrives in your code as a string you parse yourself or as an object already parsed, so check what yours does before you decode twice. If your code only passes the document to a browser, you can return prefs as it is and skip the double parse. The JSON formatter is useful to inspect what you got.

Remember that MySQL has normalized the document. The key order will be the sorted one, and numbers like 1.50 come back as 1.5. If a client signs the text of a document, store the original in a TEXT column as well.

When to stop adding JSON columns

One JSON column per table, holding what really varies, is common and healthy. It stops being healthy when:

There are several, each holding a different slice of the same row. meta, extra, config and data are four names for one undefined schema.

Every query starts with the same extraction. Move the field out, as described in the hub guide.

The column is used as a queue or a log. An array that grows by append rewrites ever-larger documents. Use rows.

Different services write different keys. Without a schema, the column becomes a place where one team's key collides with another's. Add a json_schema_valid check (validating JSON before it gets into a MySQL column) or split the data.

For the function reference, see the MySQL manual pages on the JSON data type and on JSON functions that create values.