JSON functions

JSON_ARRAY([val [, val] ...])

Returns a JSON array from the given list of values. Accepts zero or more values of any type.

SELECT JSON_ARRAY(123, "polardb-x", NULL, TRUE);
+------------------------------------------+
| JSON_ARRAY(123, 'polardb-x', NULL, true) |
+------------------------------------------+
| [123,"polardb-x",null,true]              |
+------------------------------------------+

JSON_OBJECT([key, val [, key, val] ...])

Returns a JSON object from the given list of key-value pairs. Returns an error if any key is NULL or the number of arguments is odd.

SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x');
+---------------------------------------------+
| JSON_OBJECT('id', 123, 'name', 'polardb-x') |
+---------------------------------------------+
| {"name":"polardb-x","id":123}               |
+---------------------------------------------+

JSON_QUOTE(string)

Wraps a string as a JSON string literal: encloses it in double quotation marks and escapes special characters. Returns a utf8mb4 string. Returns NULL if the argument is NULL.

The following example shows the output for a NULL input and for a string that already contains double quotation marks.

SELECT JSON_QUOTE(null), JSON_QUOTE('"abc"');
+------------------+---------------------+
| JSON_QUOTE(NULL) | JSON_QUOTE('"abc"') |
+------------------+---------------------+
| NULL             | "\"abc\""           |
+------------------+---------------------+

JSON_CONTAINS(target, candidate [, path])

Returns 1 if the candidate JSON document is contained in target, or 0 if not. With the optional path argument, checks whether candidate appears at that path in target. Returns NULL if any argument is NULL.

To check only whether a path exists (without checking the value at that path), use JSON_CONTAINS_PATH() instead.

The following examples check for a scalar value, a null value, a non-matching path, and a nested object.

SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';

-- Check that 123 exists at $.a
SELECT JSON_CONTAINS(@json_doc, '123', '$.a');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.a') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

-- Check that null exists at $.b
SELECT JSON_CONTAINS(@json_doc, 'null', '$.b');
+-----------------------------------------+
| JSON_CONTAINS(@json_doc, 'null', '$.b') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

-- Check that 123 does not exist at $.b
SELECT JSON_CONTAINS(@json_doc, '123', '$.b');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.b') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

-- Check that a nested object exists at $.c
SELECT JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c');
+-----------------------------------------------+
| JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

Returns 1 if the document contains data at any of the given paths, or 0 if not. Returns NULL if any argument is NULL.

The one_or_all argument controls matching behavior:

• 'one': returns 1 if data exists at one or more of the specified paths; 0 otherwise.

• 'all': returns 1 if data exists at all specified paths; 0 otherwise.

The following examples use a document with three keys to demonstrate 'one' and 'all' behavior.

SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';

-- $.a exists but $.e does not; 'one' returns 1
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e') |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

-- $.e does not exist; 'all' returns 0
SELECT JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e') |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+

-- $.c.d exists; 'one' returns 1
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d');
+-----------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

JSON_EXTRACT(json_doc, path[, path] ...)

Returns the value at the specified path in a JSON document. When multiple paths are given, returns an array of all matched values. Returns NULL if any argument is NULL or no path matches.

The following examples extract a single element, multiple elements, and a nested array.

-- Extract the element at index 1
SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]');
+-------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]') |
+-------------------------------------------------+
| 456                                             |
+-------------------------------------------------+

-- Extract two elements; returns an array
SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]') |
+---------------------------------------------------------+
| [123,456]                                               |
+---------------------------------------------------------+

-- Extract a scalar and a nested array
SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]') |
+---------------------------------------------------------+
| [123,[789,1000]]                                        |
+---------------------------------------------------------+

column->path

The -> operator is a shorthand for JSON_EXTRACT(). It extracts a value from a column at the given path, preserving the JSON encoding (strings remain quoted).

SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" AS NAME;
+-------------+
| NAME        |
+-------------+
| "polardb-x" |
+-------------+

column->>path

The ->> operator extends -> by also unquoting and unescaping the result. It is equivalent to JSON_UNQUOTE(JSON_EXTRACT(column, path)) or JSON_UNQUOTE(column->path).

The following example shows the difference from ->: the result is returned without surrounding quotation marks.

SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->>"$.name" AS NAME;
+-----------+
| NAME      |
+-----------+
| polardb-x |
+-----------+

JSON_KEYS(json_doc[, path])

Returns the top-level keys of a JSON object as a JSON array. With the optional path argument, returns the keys at that path. Returns NULL if any argument is NULL or the value at path is not an object.

SELECT JSON_KEYS('{"a": 123, "b": {"c": 456}}');
+------------------------------------------+
| JSON_KEYS('{"a": 123, "b": {"c": 456}}') |
+------------------------------------------+
| ["a","b"]                                |
+------------------------------------------+

JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char[, path] ...])

Returns the path (or paths) to the first occurrence of search_str in json_doc. Returns NULL if search_str is not found or any argument is NULL. Supports % and _ wildcards, the same as the LIKE operator. The escape_char argument specifies an escape character for wildcards.

PolarDB-X does not support the double asterisk (**) wildcard for paths.

The one_or_all argument controls the return value:

• 'one': returns the path of the first match. Which path is considered "first" is undefined.

• 'all': returns an array of all matching paths. The order of elements in the array is undefined.

The following examples find exact matches, no match (returns NULL), a scoped search using a path argument, and a wildcard match.

SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';

-- Find the first occurrence of "abc"
SELECT JSON_SEARCH(@json_doc, 'one', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'one', 'abc') |
+--------------------------------------+
| "$[0]"                               |
+--------------------------------------+

-- Find all occurrences of "abc"
SELECT JSON_SEARCH(@json_doc, 'all', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'abc') |
+--------------------------------------+
| ["$[0]","$[2].k2"]                   |
+--------------------------------------+

-- "xyz" is not present; returns NULL
SELECT JSON_SEARCH(@json_doc, 'all', 'xyz');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'xyz') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

-- Search only within $[*] (top-level array elements)
SELECT JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]');
+----------------------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]') |
+----------------------------------------------------+
| "$[1][1]"                                          |
+----------------------------------------------------+

-- Use % wildcard to match any string containing "a"
SELECT JSON_SEARCH(@json_doc, 'all', '%a%');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', '%a%') |
+--------------------------------------+
| ["$[0]","$[2].k2"]                   |
+--------------------------------------+

JSON_APPEND(json_doc, path, val [, path, val] ...)

JSON_APPEND() is deprecated in MySQL 5.7 and removed in MySQL 8.0. Use JSON_ARRAY_APPEND() instead.

JSON_ARRAY_APPEND(json_doc, path, val [, path, val] ...)

Appends one or more values to the end of the JSON arrays at the given paths and returns the modified document. Path-value pairs are evaluated left to right, with each result becoming the input for the next pair.

If the target at a given path is a scalar value rather than an array, the scalar is first wrapped into a single-element array before the new value is appended.

The following examples append to an existing array and to a scalar value.

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

-- Append "x" to the existing array at $.b
SELECT JSON_ARRAY_APPEND(@json_doc, '$.b', 'x');
+------------------------------------------+
| JSON_ARRAY_APPEND(@json_doc, '$.b', 'x') |
+------------------------------------------+
| {"a":1,"b":[2,3,"x"],"c":4}              |
+------------------------------------------+

-- Append "y" to the scalar at $.c; the scalar is wrapped into an array first
SELECT JSON_ARRAY_APPEND(@json_doc, '$.c', 'y');
+------------------------------------------+
| JSON_ARRAY_APPEND(@json_doc, '$.c', 'y') |
+------------------------------------------+
| {"a":1,"b":[2,3],"c":[4,"y"]}            |
+------------------------------------------+

JSON_ARRAY_INSERT(json_doc, path, val [, path, val] ...)

Inserts one or more values into JSON arrays at specific positions and returns the modified document. If the array subscript in the path exceeds the array length, the value is appended as the last element. Path-value pairs are evaluated left to right.

The following examples insert at a specific index, beyond the array bounds, into a nested array, and with two path-value pairs.

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

-- Insert "x" before index 1
SELECT JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x');
+-------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x') |
+-------------------------------------------+
| ["a","x",{"b":[1,2]},[3,4]]               |
+-------------------------------------------+

-- Index 10 is beyond the array; "x" is appended at the end
SELECT JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x');
+--------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x') |
+--------------------------------------------+
| ["a",{"b":[1,2]},[3,4],"x"]                |
+--------------------------------------------+

-- Insert "x" into the nested array at $[1].b before index 1
SELECT JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x');
+------------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x') |
+------------------------------------------------+
| ["a",{"b":[1,"x",2]},[3,4]]                    |
+------------------------------------------------+

-- Apply two insertions sequentially; the second path reflects the shifted indices after the first insertion
SELECT JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y');
+-----------------------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y') |
+-----------------------------------------------------------+
| ["x","a",{"b":[1,2]},[3,"y",4]]                           |
+-----------------------------------------------------------+

JSON_INSERT(json_doc, path, val [, path, val] ...)

Inserts values into a JSON document without overwriting existing values. Path-value pairs are evaluated left to right.

The insertion rules are:

• If the path already has a value, the pair is ignored and the existing value is preserved.

• If the path does not exist:

  • For a JSON object: adds the new key-value pair.

  • For an array subscript beyond the current array length: appends the value. A scalar is wrapped as a single-element array.

  • Otherwise: the pair is ignored.

The following example shows that $.a already exists and is not overwritten, while the new key $.c is inserted.

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

SELECT JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------------+
| JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------+
| {"a":1,"b":[2,3],"c":"[true, false]"}                     |
+-----------------------------------------------------------+

For a comparison of JSON_INSERT(), JSON_SET(), and JSON_REPLACE(), see the table below.

JSON_MERGE(json_doc, json_doc [, json_doc] ...)

JSON_MERGE() is deprecated in MySQL 5.7 and removed in MySQL 8.0.3. Use JSON_MERGE_PRESERVE() instead. The two functions behave identically.

JSON_MERGE_PATCH(json_doc, json_doc [, json_doc] ...)

Merges two or more JSON documents, removing duplicate keys. Documents are merged left to right using the following rules:

1. If doc1 is not an object, the result is doc2.

2. If doc2 is not an object, the result is doc2.

3. If both are objects, the result contains:

   • Members from doc1 with no matching key in doc2.

   • Members from doc2 with no matching key in doc1, where the value is not a JSON null literal.

   • For keys present in both: the value from doc2 is used (recursively merged), unless the doc2 value is a JSON null literal, in which case the key is removed.

The following examples show merging distinct keys, using null to remove a key, and a three-way merge.

-- Merge two objects with distinct keys
SELECT JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}');
+----------------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}') |
+----------------------------------------------------------+
| {"name":"polardb-x","id":123}                            |
+----------------------------------------------------------+

-- A null value in the second document removes the key "b"
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a":1}                                          |
+--------------------------------------------------+

-- Three-way merge; "a" takes the value from the last document
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
+-----------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+-----------------------------------------------------------------------------------+
| {"a":5,"b":2,"c":4,"d":6}                                                         |
+-----------------------------------------------------------------------------------+

JSON_MERGE_PRESERVE(json_doc, json_doc [, json_doc] ...)

Merges two or more JSON documents, retaining all members including those with duplicate keys. Documents are merged using the following rules:

• Adjacent arrays are merged into a single array.

• Adjacent objects are merged into a single object.

• A scalar is wrapped as a single-element array before merging.

• When merging an object with an array, the object is wrapped as a single-element array first.

The following examples contrast JSON_MERGE_PRESERVE() with JSON_MERGE_PATCH() for the same inputs.

-- Merging two objects with distinct keys (same result as PATCH)
SELECT JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}');
+-------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}') |
+-------------------------------------------------------------+
| {"name":"polardb-x","id":123}                               |
+-------------------------------------------------------------+

-- Duplicate key "b": PRESERVE keeps both values as an array (vs. PATCH which removes "b")
SELECT JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}') |
+-----------------------------------------------------+
| {"a":1,"b":[2,null]}                                |
+-----------------------------------------------------+

-- Three-way merge: all values for "a" are collected into an array
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
+------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+------------------------------------------------------------------------------------+
| {"a":[1,3,5],"b":2,"c":4,"d":6}                                                    |
+------------------------------------------------------------------------------------+

JSON_REMOVE(json_doc, path [, path] ...)

Removes elements at the given paths from a JSON document and returns the modified document. Path arguments are evaluated left to right, with each result becoming the input for the next path. If an element does not exist at the given path, no error is returned.

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

-- Remove the element at index 1 (the nested array ["b","c"])
SELECT JSON_REMOVE(@json_doc, '$[1]');
+--------------------------------+
| JSON_REMOVE(@json_doc, '$[1]') |
+--------------------------------+
| ["a","d"]                      |
+--------------------------------+

JSON_REPLACE(json_doc, path, val [, path, val] ...)

Replaces existing values at the given paths in a JSON document. Path-value pairs are evaluated left to right. If no value exists at the specified path, the pair is ignored and no error is returned.

The following example shows that $.a is replaced while $.c (which does not exist) is silently ignored.

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

SELECT JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]');
+------------------------------------------------------------+
| JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]') |
+------------------------------------------------------------+
| {"a":"10","b":[2,3]}                                       |
+------------------------------------------------------------+

For a comparison of JSON_INSERT(), JSON_SET(), and JSON_REPLACE(), see the behavior table under JSON_INSERT().

JSON_SET(json_doc, path, val [, path, val] ...)

Inserts or updates values at the given paths in a JSON document. Path-value pairs are evaluated left to right.

The insertion and update rules are:

• If the path already has a value, the existing value is overwritten.

• If the path does not exist:

  • For a JSON object: adds the new key-value pair.

  • For an array subscript beyond the current array length: appends the value. A scalar is wrapped as a single-element array.

  • Otherwise: the pair is ignored.

The following example shows that $.a is overwritten and the new key $.c is inserted.

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

SELECT JSON_SET(@json_doc, '$.a', 10, '$.c', '[true, false]');
+---------------------------------------------------------+
| JSON_SET(@json_doc, '$.a', 10, '$.c', '[true, false]') |
+---------------------------------------------------------+
| {"a":10,"b":[2,3],"c":[true,false]}                     |
+---------------------------------------------------------+

For a comparison of JSON_INSERT(), JSON_SET(), and JSON_REPLACE(), see the behavior table under JSON_INSERT().

JSON_UNQUOTE(json_val)

Unquotes a JSON string value, interprets escape sequences, and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL.

The following examples show unquoting a plain string, a string with a tab escape, and a Unicode escape sequence.

-- Unquote a plain string
SELECT JSON_UNQUOTE('"abc"');
+-----------------------+
| JSON_UNQUOTE('"abc"') |
+-----------------------+
| abc                   |
+-----------------------+

-- \t is interpreted as a tab character
SELECT JSON_UNQUOTE('"a\\tbc"');
+--------------------------+
| JSON_UNQUOTE('"a\\tbc"') |
+--------------------------+
| a	bc                     |
+--------------------------+

-- \t and Unicode escape \u0032 (the digit "2")
SELECT JSON_UNQUOTE('"\\t\\u0032"');
+------------------------------+
| JSON_UNQUOTE('"\\t\\u0032"') |
+------------------------------+
| 	2                          |
+------------------------------+

JSON_DEPTH(json_doc)

Returns the maximum nesting depth of a JSON document as an integer. Returns NULL if the argument is NULL.

Depth is calculated as follows:

• An empty array, empty object, or scalar value has depth 1.

• An array with a single element has depth 1.

• A JSON object where every member has depth 1 has depth 2.

The following example returns a depth of 3: the top-level array (depth 1) contains an integer (depth 1) and an object (depth 2), so the overall depth is 3.

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

JSON_LENGTH(json_doc [, path])

Returns the length of a JSON document. Returns NULL if any argument is NULL. With the optional path argument, returns the length of the value at that path.

Length is calculated as follows:

• A scalar value has length 1.

• A JSON array's length is the number of elements.

• A JSON object's length is the number of top-level members.

• Nested arrays or objects do not contribute to the length of the parent.

The following example returns 2 because the top-level object has two members (a and b); the nested object {"c": 30} is not counted.

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

JSON_TYPE(json_val)

Returns the type of a JSON value as a utf8mb4 string. Returns NULL if the argument is NULL.

Common return values include: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, BOOLEAN, NULL.

The following examples check the type of an array, an integer element, and a boolean element.

SET @json_obj = '{"a": [10, true]}';

-- The value at $.a is an array
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a'));
+-------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a')) |
+-------------------------------------------+
| ARRAY                                     |
+-------------------------------------------+

-- The first element of the array is an integer
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]')) |
+----------------------------------------------+
| INTEGER                                      |
+----------------------------------------------+

-- The second element of the array is a boolean
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]')) |
+----------------------------------------------+
| BOOLEAN                                      |
+----------------------------------------------+

JSON_VALID(val)

Returns 1 if val is a valid JSON value, or 0 if not. Returns NULL if the argument is NULL.

Note that an unquoted string like hello is not valid JSON, but a quoted string like "hello" is.

SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

JSON_PRETTY(json_doc)

Returns the JSON document formatted for readability, with indentation and newlines. Returns NULL if the argument is NULL.

SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';

SELECT JSON_PRETTY(@json_doc);
+---------------------------------------------------------------------------+
| JSON_PRETTY(@json_doc)                                                    |
+---------------------------------------------------------------------------+
| [
    "abc",
    [
        {
            "k1":123
        },
        "def"
    ],
    {
        "k2":"abc"
    },
    {
        "k3": null
    }
] |
+---------------------------------------------------------------------------+

JSON_STORAGE_SIZE(json_doc)

Returns the number of bytes used to store the binary representation of a JSON document. Returns NULL if the argument is NULL.

SET @json_doc = '[999, "polardb-x", [1, 2, 3], 888.88]';

SELECT JSON_STORAGE_SIZE(@json_doc) AS Size;
+------+
| Size |
+------+
|   48 |
+------+