This topic describes the JSON functions that are supported by PolarDB-X.

Overview

PolarDB-X supports all JSON functions that are supported by MySQL 5.7. The following table divides the functions into five categories. For more information, see JSON functions.

Category Function
Functions that create JSON values JSON_ARRAY
JSON_OBJECT
JSON_QUOTE
Functions that search JSON values JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_EXTRACT
->
->>
JSON_KEYS
JSON_SEARCH
Functions that modify JSON values JSON_APPEND
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_INSERT
JSON_MERGE
JSON_MERGE_PATCH
JSON_MERGE_PRESERVE
JSON_REMOVE
JSON_REPLACE
JSON_SET
JSON_UNQUOTE
Functions that return the attributes of JSON values JSON_DEPTH
JSON_LENGTH
JSON_TYPE
JSON_VALID
JSON utility functions JSON_PRETTY
JSON_STORAGE_SIZE

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

  • Description: This function evaluates a list of values and returns a JSON array that contains all these values.
  • Sample code:
    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] ...])

  • Description: This function evaluates a list of key-value pairs and returns a JSON object that contains all these key-value pairs. An error is returned if the name of a key is NULL or the number of arguments is odd.
  • Sample code:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x');
+---------------------------------------------+
| JSON_OBJECT('id', 123, 'name', 'polardb-x') |
+---------------------------------------------+
| {"name":"polardb-x","id":123}               |
+---------------------------------------------+

JSON_QUOTE(string)

  • Description: This function encloses a string as a JSON value in a pair of double quotation marks (""), escapes special characters, and returns a utf8mb4 string as the result. If the argument is NULL, this function returns NULL. This function is often used to produce a valid JSON string literal.
  • Sample code:
    SELECT JSON_QUOTE(null), JSON_QUOTE('"abc"');
+------------------+---------------------+
| JSON_QUOTE(NULL) | JSON_QUOTE('"abc"') |
+------------------+---------------------+
| NULL             | ""abc""             |
+------------------+---------------------+

JSON_CONTAINS(target, candidate [, path])

  • Description: This function returns 1 or 0 to indicate whether a candidate JSON document is contained in a target JSON document. If the path argument is specified, the function returns 1 or 0 to indicate whether a candidate JSON document can be found in the path in a target JSON document. To check only whether a candidate JSON document contains data in one or more given paths, use the JSON_CONTAINS_PATH() function. For more information, see JSON document.
  • Sample code:
    SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
SELECT JSON_CONTAINS(@json_doc, '123', '$.a');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.a') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
SELECT JSON_CONTAINS(@json_doc, 'null', '$.b');
+-----------------------------------------+
| JSON_CONTAINS(@json_doc, 'null', '$.b') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
SELECT JSON_CONTAINS(@json_doc, '123', '$.b');
+----------------------------------------+
| JSON_CONTAINS(@json_doc, '123', '$.b') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
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] ...)

  • This function returns 1 or 0 to indicate whether the json_doc document contains data in one or more given paths. Valid values of the one_or_all argument:
    • 'one': This function returns 1 if the json_doc document contains data in one or more paths that are specified in the path arguments. The function returns 0 in other conditions.
    • 'all': This function returns 1 if the json_doc document contains data in all paths that are specified in the path arguments. The function returns 0 in other conditions.
  • Sample code:
    SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e') |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
SELECT JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e');
+----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e') |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d');
+-----------------------------------------------+
| JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

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

  • Description: This function returns the values that match the path arguments from a JSON document.
  • Sample code:
    SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]');
+-------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]') |
+-------------------------------------------------+
| 456                                             |
+-------------------------------------------------+
SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]') |
+---------------------------------------------------------+
| [123,456]                                               |
+---------------------------------------------------------+
SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]');
+---------------------------------------------------------+
| JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]') |
+---------------------------------------------------------+
| [123,[789,1000]]                                        |
+---------------------------------------------------------+

column->path

  • Description: The -> operator works in the same manner as the JSON_EXTRACT() function and returns the column values based on the path argument.
  • Sample code:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
+-------------+
| NAME        |
+-------------+
| "polardb-x" |
+-------------+

column->>path

  • Description: The ->> operator is an improved operator as compared to the -> operator. The ->> operator unquotes and unescapes the return values. For more information, see JSON_UNQUOTE. The ->> operator works in the same manner as the following function expressions:
    • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
    • JSON_UNQUOTE( column -> path )
  • Sample code:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
+-------------+
| NAME        |
+-------------+
| "polardb-x" |
+-------------+

JSON_KEYS(json_doc[, path])

  • Description: This function returns the keys from the top-level value of the json_doc object as a JSON array.
  • Sample code:
    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] ...])

  • This function returns one or more paths to the search_str string in the json_doc document. If no paths are found, the function returns NULL. The search_str string supports wildcards for fuzzy matches. The supported wildcards include the percent signs (%) and the underscores (_) that are used in conjunction with the LIKE operator. The escape_char argument specifies an escape character. Valid values of the one_or_all argument:
    • 'one': This function returns the first path in which the search_str string is found. The path that is considered the first path to return is undefined.
    • 'all': This function returns all paths in which the search_str string is found. The order of elements in the array is undefined.
      Note PolarDB-X does not allow you to use a double asterisk (**) wildcard for paths.
  • Sample code:
    SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';
SELECT JSON_SEARCH(@json_doc, 'one', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'one', 'abc') |
+--------------------------------------+
| "$[0]"                               |
+--------------------------------------+
SELECT JSON_SEARCH(@json_doc, 'all', 'abc');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'abc') |
+--------------------------------------+
| ["$[0]","$[2].k2"]                   |
+--------------------------------------+
SELECT JSON_SEARCH(@json_doc, 'all', 'xyz');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'xyz') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
SELECT JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]');
+----------------------------------------------------+
| JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]') |
+----------------------------------------------------+
| "$[1][1]"                                          |
+----------------------------------------------------+
SELECT JSON_SEARCH(@json_doc, 'all', '%a%');
+--------------------------------------+
| JSON_SEARCH(@json_doc, 'all', '%a%') |
+--------------------------------------+
| ["$[0]","$[2].k2"]                   |
+--------------------------------------+

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

This function is renamed to JSON_ARRAY_APPEND(). For more information, see JSON_ARRAY_APPEND.
Note This function is deprecated in MySQL 5.7 and is removed in MySQL 8.0.

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

  • Description: This function appends values to the end of the JSON arrays in the json_doc document and returns the modified json_doc document. The path-value pairs are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated.
  • Sample code:
    SET @json_doc = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@json_doc, '$.b', 'x');
+------------------------------------------+
| JSON_ARRAY_APPEND(@json_doc, '$.b', 'x') |
+------------------------------------------+
| {"a":1,"b":[2,3,"x"],"c":4}              |
+------------------------------------------+
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] ...)

  • Description: This function inserts values into the JSON arrays at specific positions in the json_doc document and returns the modified json_doc document. If the value of the array position specified in a path argument exceeds the array size, the value is appended as the last element of the array. The path-value pairs are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated.
  • Sample code:
    SET @json_doc = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x');
+-------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x') |
+-------------------------------------------+
| ["a","x",{"b":[1,2]},[3,4]]               |
+-------------------------------------------+
SELECT JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x');
+--------------------------------------------+
| JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x') |
+--------------------------------------------+
| ["a",{"b":[1,2]},[3,4],"x"]                |
+--------------------------------------------+
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]]                    |
+------------------------------------------------+
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] ...)

  • Description: This function inserts values into specific paths in the json_doc argument. The path-value pairs are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated. The function inserts values into specific paths based on the following conditions:
    • If the path that you specify in a path-value pair has an existing value, the function ignores the path-value pair and does not overwrite the existing value.
    • If the path that you specify in a path-value pair has no value, the function inserts data based on the following conditions:
      • A JSON object is a collection of key-value pairs. Each key-value pair represents a member of a JSON object. In this case, if the member that you specify does not exist in a JSON object, the function adds the member to the object.
      • If the path argument specifies an array subscript that is above the array bound of the json_doc argument, the val argument is added to the end of the specified array. A scalar value is wrapped as an array that contains one element.
      • In other conditions, the path-value pair is ignored and no operations are performed.

    For information about the differences among the JSON_REPLACE(), JSON_SET(), and JSON_INSERT() functions, see JSON_REPLACE and JSON_SET.

  • Sample code:
    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]"}                     |
+-----------------------------------------------------------+

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

This function works in the same manner as the JSON_MERGE_PRESERVE () function. Note that the JSON_MERGE () function is deprecated in MySQL 5.7 and is removed in MySQL 8.0.3.

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

  • This function merges multiple JSON documents and removes the members that have duplicate keys. This function merges two JSON documents from left to right in sequence. For example, if you use the JSON_MERGE_PATCH() function to merge two JSON documents named doc 1 and doc 2, the following rules are used:
    1. If doc 1 is not an object, the result of the merge is doc 2.
    2. If doc 2 is not an object, the result of the merge is doc 2.
    3. If doc 1 and doc 2 are objects, the result of the merge is an object that has the following members:
      • The members of doc 1 that do not have a corresponding key in doc 2.
      • The members of doc 2 that do not have a corresponding key in doc 1 and the key value is not a JSON null literal.
      • The members that have the same key in doc 1 and doc 2 and the key value in doc 2 is not a JSON null literal. The values of these members are the results of recursively merging the value in doc 1 with the value in doc 2 based on the preceding rules.

      The members of doc 2 that have a corresponding key in doc 1 are retained based on the preceding rules.

  • Sample code:
    SELECT JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}');
+----------------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}') |
+----------------------------------------------------------+
| {"name":"polardb-x","id":123}                            |
+----------------------------------------------------------+
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a":1}                                          |
+--------------------------------------------------+
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] ...)

  • Description:

    This function merges multiple JSON documents and retains all members in the documents. This function does not remove the members that have duplicate keys. This function merges JSON documents based on the following rules:

    • Adjacent arrays are merged into a single array.
    • Adjacent objects are merged into a single object.
    • A scalar value is wrapped as an array and merged as an array.
    • If an object and an array are to be merged, the object is wrapped as an array that contains a single element and then merged with the other array.
  • For information about the differences between the JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() functions, see JSON_MERGE_PATCH. Sample code:
    SELECT JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}');
+-------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}') |
+-------------------------------------------------------------+
| {"name":"polardb-x","id":123}                               |
+-------------------------------------------------------------+
SELECT JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}') |
+-----------------------------------------------------+
| {"a":1,"b":[2,null]}                                |
+-----------------------------------------------------+
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] ...)

  • Description: This function removes elements from specific paths in a JSON document. The path arguments are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated. If the specified element does not exist in the specified path, no error is returned.
  • Sample code:
    SET @json_doc = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@json_doc, '$[1]');
+--------------------------------+
| JSON_REMOVE(@json_doc, '$[1]') |
+--------------------------------+
| ["a","d"]                      |
+--------------------------------+

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

  • Description: This function replaces values that exist in specified paths in a JSON document. The path-value pairs are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated. If the specified element does not exist in the specified path, no error is returned.
  • Sample code:
    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]}                                       |
+------------------------------------------------------------+

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

  • Description: This function inserts values into specified paths or updates values in specified paths in a JSON document. The path-value pairs are evaluated from left to right in sequence. The JSON document that is produced by evaluating one path-value pair becomes the new value against which the next path-value pair is evaluated. This function inserts or updates values based on the following rules:
    • If the path that you specify in a path-value pair has an existing value, the function overwrites the existing value with the new value.
    • If the path that you specify in a path-value pair has no value, the function inserts data based on the following conditions:
      • A JSON object is a collection of key-value pairs. Each key-value pair represents a member of a JSON object. In this case, if the member that you specify does not exist in a JSON object, the function adds the member to the object.
      • If the path argument specifies an array subscript that is above the array bound of the json_doc argument, the val argument is added to the end of the specified array. A scalar value is wrapped as an array that contains one element.
      • In other conditions, the path-value pair is ignored and no operations are performed.
  • Sample code:
    SET @json_doc = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_SET(@json_doc, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a":10,"b":[2,3],"c":[true,false]}             |
+-------------------------------------------------+

JSON_UNQUOTE(json_val)

  • Description: This function unquotes the json_val argument, interprets escaped characters as if they were not escaped, and returns a utf8mb4 string as the result.
  • Sample code:
    SELECT JSON_UNQUOTE('"abc"');
+-----------------------+
| JSON_UNQUOTE('"abc"') |
+-----------------------+
| abc                   |
+-----------------------+
SELECT JSON_UNQUOTE('"a\\tbc"');
+--------------------------+
| JSON_UNQUOTE('"a\\tbc"') |
+--------------------------+
| a    bc                     |
+--------------------------+
SELECT JSON_UNQUOTE('"\\t\\u0032"');
+------------------------------+
| JSON_UNQUOTE('"\\t\\u0032"') |
+------------------------------+
|     2                           |
+------------------------------+

JSON_DEPTH(json_doc)

  • Description:

    This function returns the maximum depth of the json_doc argument. The function returns a result based on the following rules:

    • The depth of an empty array, an empty object, or a scalar value is 1.
    • The depth of an array that contains only one element is 1.
    • If the depth of each member in a JSON object is 1, the depth of the JSON object is 2.
  • Sample code:
    SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
|                             3 |
+-------------------------------+

JSON_LENGTH(json_doc [, path])

  • Description:

    This function returns the length of the json_doc argument. The function returns a result based on the following rules:

    • The length of a scalar value is 1.
    • The length of an array is the number of array elements.
    • The length of an object is the number of object members.
    • The length of the json_doc argument does not include the length of nested arrays or objects.
  • Sample code:
    SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+

JSON_TYPE(json_val)

  • Description: This function returns the type of the json_val argument.
  • Sample code:
    SET @json_obj = '{"a": [10, true]}';
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a'));
+-------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a')) |
+-------------------------------------------+
| ARRAY                                     |
+-------------------------------------------+
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]')) |
+----------------------------------------------+
| INTEGER                                      |
+----------------------------------------------+
SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]'));
+----------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]')) |
+----------------------------------------------+
| BOOLEAN                                      |
+----------------------------------------------+

JSON_VALID(val)

  • Description: This function returns 0 or 1 to indicate whether the val argument is a valid JSON value.
  • Sample code:
    SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

JSON_PRETTY(json_doc)

  • Description: This function prints out the json_doc argument in a format that is easy to read by indenting lines and inserting newlines.
  • Sample code:
    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"
    },
    {
    }
] |
+---------------------------------------------------------------------------+

JSON_STORAGE_SIZE(json_doc)

  • Description: This function returns the number of bytes that are used to store the binary representation of the json_doc argument.
  • Sample code:
    SET @json_doc = '[999, "polardb-x", [1, 2, 3], 888.88]';
SELECT JSON_STORAGE_SIZE(@json_doc) AS Size;
+------+
| Size |
+------+
|   48 |
+------+