JSON

MySQL stores JSON in a native binary format and provides a large family of functions for reading, editing, and searching it. For aggregating rows into a JSON array or object, see JSON_ARRAYAGG() and JSON_OBJECTAGG() on the Aggregate page.

Read and Write

JSON_EXTRACT(json_doc, path), or the -> operator, returns the value at a JSON path as a JSON value. JSON_UNQUOTE(json_val), or the ->> operator (which combines extraction and unquoting), returns a scalar as a plain SQL string rather than a quoted JSON value. JSON_QUOTE(str) quotes a string as a JSON value. JSON_VALUE(json_doc, path) extracts a scalar directly.

SELECT c->'$.name' AS name FROM jemp;
SELECT JSON_EXTRACT(c, '$.name') AS name FROM jemp;

For c = {"id":"3","name":"Barney"}, both return the quoted JSON string "Barney".

SELECT c->>'$.name' AS name FROM jemp;
SELECT JSON_UNQUOTE(c->'$.name') AS name FROM jemp;

Both return the plain string Barney, without surrounding quotes.

CREATE TABLE jemp (c JSON, g INT);
INSERT INTO jemp VALUES
    ('{"id": "3", "name": "Barney"}', 3),
    ('{"id": "4", "name": "Betty"}', 4);

SELECT c->'$.name' AS name FROM jemp;

SELECT c->>'$.name' AS name FROM jemp;

CREATE TABLE jemp (c JSON, g INT);
INSERT INTO jemp VALUES
    ('{"id": "3", "name": "Barney"}', 3),
    ('{"id": "4", "name": "Betty"}', 4);

SELECT c->'$.name' AS name FROM jemp;

SELECT c->>'$.name' AS name FROM jemp;

SELECT c->'$.name' AS name FROM jemp:

name
"Barney"
"Betty"

SELECT c->>'$.name' AS name FROM jemp:

name
Barney
Betty

JSON_INSERT(json_doc, path, val, ...) adds a value only if the path does not already exist. JSON_SET(json_doc, path, val, ...) inserts or overwrites. JSON_REPLACE(json_doc, path, val, ...) overwrites existing paths only, leaving nonexistent ones untouched. JSON_REMOVE(json_doc, path, ...) deletes the value(s) at the given path(s). None of these mutate their input in place — each returns a new JSON value.

SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false, false]');
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
SELECT JSON_REMOVE(@j, '$.b');

{"a": 1, "b": [2, 3], "c": "[true, false]"} (no overwrite of existing $.a); {"a": 10, "b": [2, 3], "c": "[true, false, false]"} (overwrites); {"a": 10, "b": [2, 3]} (only $.a exists, so only it is overwritten); {"a": 1} — and @j itself is unchanged by all four calls.

SET @j = '{ "a": 1, "b": [2, 3]}';

SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');

SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false, false]');

SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

SELECT JSON_REMOVE(@j, '$.b');

SET @j = '{ "a": 1, "b": [2, 3]}';

SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');

SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false, false]');

SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

SELECT JSON_REMOVE(@j, '$.b');

SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'):

JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]')
{"a": 1, "b": [2, 3], "c": "[true, false]"}

SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false, false]'):

JSON_SET(@j, '$.a', 10, '$.c', '[true, false, false]')
{"a": 10, "b": [2, 3], "c": "[true, false, false]"}

SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'):

JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]')
{"a": 10, "b": [2, 3]}

SELECT JSON_REMOVE(@j, '$.b'):

JSON_REMOVE(@j, '$.b')
{"a": 1}

Object and Array Construction

JSON_OBJECT(key, val, ...) and JSON_ARRAY(val, ...) build a JSON object/array from SQL values.

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());

{"id": 87, "name": "carrot"}; [1, "abc", null, true, "11:30:24.000000"]

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

JSON_OBJECT('id', 87, 'name', 'carrot')
{"id": 87, "name": "carrot"}

JSON_ARRAY_APPEND(json_doc, path, val, ...) appends a value to the array at path (or wraps a non-array scalar at that path into a new array first). JSON_ARRAY_INSERT(json_doc, path, val, ...) inserts a value at a specific array index, shifting later elements.

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x'); -- on a different object doc

["a", ["b", "c", 1], "d"] — appends inside the nested array at $[1]; appending to a non-array scalar path instead wraps it (e.g. on {"a":1,"b":[2,3],"c":4}, appending at $.c produces {"a":1,"b":[2,3],"c":[4,"y"]}).

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');

["a", "x", {"b": [1, 2]}, [3, 4]] — inserting at an out-of-range index (e.g. $[100]) appends at the end instead.

SET @j = '["a", ["b", "c"], "d"]';

SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');

SET @j = '["a", ["b", "c"], "d"]';

SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');

SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1):

JSON_ARRAY_APPEND(@j, '$[1]', 1)
["a", ["b", "c", 1], "d"]

SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'):

JSON_ARRAY_INSERT(@j, '$[1]', 'x')
["a", "x", {"b": [1, 2]}, [3, 4]]

Search and Merge

JSON_SEARCH(json_doc, 'one'|'all', search_str) returns the path(s) of matching string values. JSON_CONTAINS(json_doc, val [, path]) tests whether json_doc contains val. JSON_CONTAINS_PATH (json_doc, 'one'|'all', path, ...) tests whether any/all of the given paths exist. JSON_OVERLAPS(doc1, doc2) tests whether two documents share any key/value pair or array element.

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc');
SELECT JSON_SEARCH(@j, 'all', 'abc');

"$[0]"; ["$[0]", "$[2].x"]

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'one', 'abc');

SELECT JSON_SEARCH(@j, 'all', 'abc');

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'one', 'abc');

SELECT JSON_SEARCH(@j, 'all', 'abc');

SELECT JSON_SEARCH(@j, 'one', 'abc'):

JSON_SEARCH(@j, 'one', 'abc')
"$[0]"

SELECT JSON_SEARCH(@j, 'all', 'abc'):

JSON_SEARCH(@j, 'all', 'abc')
["$[0]", "$[2].x"]

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS(@j, '1', '$.a');
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
SELECT JSON_OVERLAPS('[1,3,5,7]', '[2,5,7]'), JSON_OVERLAPS('[1,3,5,7]', '[2,6,8]');

1; 1 (at least $.a exists); 0 (not every path exists, since $.e does not); 1, 0

JSON_MERGE_PATCH(doc1, doc2, ...) merges documents RFC 7396-style — later scalar/object keys overwrite earlier ones, and a NULL value deletes a key. JSON_MERGE_PRESERVE(doc1, doc2, ...) instead combines conflicting values into an array rather than overwriting.

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }');

{"a": 3, "b": 2, "c": 4} (patch overwrites "a"); {"a": [1, 3], "b": 2, "c": 4} (preserve keeps both values for "a" in an array)

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS(@j, '1', '$.a');

SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');

SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');

SELECT JSON_OVERLAPS('[1,3,5,7]', '[2,5,7]'), JSON_OVERLAPS('[1,3,5,7]', '[2,6,8]');

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }');

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS(@j, '1', '$.a');

SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');

SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');

SELECT JSON_OVERLAPS('[1,3,5,7]', '[2,5,7]'), JSON_OVERLAPS('[1,3,5,7]', '[2,6,8]');

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');

SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }');

SELECT JSON_CONTAINS(@j, '1', '$.a'):

JSON_CONTAINS(@j, '1', '$.a')
1

SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'):

JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e')
1

SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'):

JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e')
0

SELECT JSON_OVERLAPS('[1,3,5,7]', '[2,5,7]'), JSON_OVERLAPS('[1,3,5,7]', '[2,6,8]'):

JSON_OVERLAPS('[1,3,5,7]', '[2,5,7]')	JSON_OVERLAPS('[1,3,5,7]', '[2,6,8]')
1	0

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'):

JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }')
{"a": 3, "b": 2, "c": 4}

SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }'):

JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }')
{"a": [1, 3], "b": 2, "c": 4}

Metadata

JSON_DEPTH(json_doc) returns the maximum nesting depth. JSON_KEYS(json_doc [, path]) returns the top-level keys of an object as a JSON array. JSON_LENGTH(json_doc [, path]) returns the number of elements/members. JSON_TYPE(json_val) returns a string describing the value's JSON type (e.g. OBJECT, ARRAY, STRING). JSON_VALID(val) returns 1 if val parses as valid JSON. val MEMBER OF(json_array) tests array membership.

SELECT JSON_DEPTH('[10, {"a": 20}]');
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
SELECT JSON_TYPE('{"a": [10, true]}');
SELECT JSON_VALID('{"a": 1}'), JSON_VALID('hello'), JSON_VALID('"hello"');
SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');

3; ["c"]; 2; 'OBJECT'; 1, 0, 1; 1

SELECT JSON_DEPTH('[10, {"a": 20}]');

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');

SELECT JSON_TYPE('{"a": [10, true]}');

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('hello'), JSON_VALID('"hello"');

SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');

SELECT JSON_DEPTH('[10, {"a": 20}]');

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');

SELECT JSON_TYPE('{"a": [10, true]}');

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('hello'), JSON_VALID('"hello"');

SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');

SELECT JSON_DEPTH('[10, {"a": 20}]'):

JSON_DEPTH('[10, {"a": 20}]')
3

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'):

JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b')
["c"]

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'):

JSON_LENGTH('{"a": 1, "b": {"c": 30}}')
2

SELECT JSON_TYPE('{"a": [10, true]}'):

JSON_TYPE('{"a": [10, true]}')
OBJECT

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('hello'), JSON_VALID('"hello"'):

JSON_VALID('{"a": 1}')	JSON_VALID('hello')	JSON_VALID('"hello"')
1	0	1

SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]'):

'ab' MEMBER OF('[23, "abc", 17, "ab", 10]')
1

JSON_TABLE() and Formatting

JSON_TABLE(json_doc, path COLUMNS(...)) converts a JSON array into a relational result set, one row per array element, with each COLUMNS entry extracting a value (optionally with DEFAULT ... ON EMPTY / ON ERROR fallbacks) or testing existence via EXISTS PATH. JSON_PRETTY(json_val) returns an indented, human-readable rendering of a JSON value. JSON_STORAGE_SIZE(json_col) and JSON_STORAGE_FREE(json_col) report the binary storage size of a JSON column value, and how much space an in-place update via JSON_SET()/JSON_REPLACE()/JSON_REMOVE() freed, respectively.

SELECT * FROM
  JSON_TABLE(
    '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    "$[*]"
    COLUMNS(
      rowid FOR ORDINALITY,
      ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
      aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
      bx INT EXISTS PATH "$.b"
    )
  ) AS tt;

Five rows — rows lacking an "a" key fall back to the DEFAULT ON EMPTY value ('111' for ac, {"x": 333} for aj), and a row whose "a" is itself an array falls back to DEFAULT ON ERROR ('999') for the VARCHAR column ac (since an array can't be cast to VARCHAR), while the JSON column aj holds the array natively without error; bx is 1 only for the row that has a "b" key.

SELECT * FROM
    JSON_TABLE(
        '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
        "$[*]"
        COLUMNS(
            rowid FOR ORDINALITY,
            ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
            aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
            bx INT EXISTS PATH "$.b"
        )
    ) AS tt;

SELECT * FROM
    JSON_TABLE(
        '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
        "$[*]"
        COLUMNS(
            rowid FOR ORDINALITY,
            ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
            aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
            bx INT EXISTS PATH "$.b"
        )
    ) AS tt;

rowid	ac	aj	bx
1	3	"3"	0
2	2	2	0
3	111	{"x": 333}	1
4	0	0	0
5	999	[1, 2]	0

An in-place update via JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() can shrink a JSON column's binary representation without allocating a new value; JSON_STORAGE_SIZE() and JSON_STORAGE_FREE() report the resulting size and freed space:

CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES
('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
UPDATE jtable
SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
SELECT JSON_STORAGE_SIZE(jcol), JSON_STORAGE_FREE(jcol) FROM jtable;

48, 14 — the update replaces $.c's string value "[true, false]" with the much shorter integer 1, freeing 14 bytes.

JSON_PRETTY(json_val) returns an indented, human-readable rendering of a JSON value:

SELECT JSON_PRETTY('["a",1,{"key1":"value1"},"5","77",
{"key2":["value3","valuex","valuey"]},"j","2"]');

CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES
    ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');

UPDATE jtable
    SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);

SELECT JSON_STORAGE_SIZE(jcol), JSON_STORAGE_FREE(jcol) FROM jtable;

CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES
    ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');

UPDATE jtable
    SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);

SELECT JSON_STORAGE_SIZE(jcol), JSON_STORAGE_FREE(jcol) FROM jtable;

Query OK, 1 row affected

JSON_STORAGE_SIZE(jcol)	JSON_STORAGE_FREE(jcol)
48	14

SELECT JSON_PRETTY('["a",1,{"key1":"value1"},"5","77",
    {"key2":["value3","valuex","valuey"]},"j","2"]');

SELECT JSON_PRETTY('["a",1,{"key1":"value1"},"5","77",
    {"key2":["value3","valuex","valuey"]},"j","2"]');

JSON_PRETTY(...)
[ "a", 1, { "key1": "value1" }, "5", "77", { "key2": [ "value3", "valuex", "valuey" ] }, "j", "2" ]

String	<<<	Data Manipulation Language	>>>	Date and Time
last update: 17 Aug 2026

MENU