AnalyticDB:JSON functions

Functions at a glance

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

Returns 1 if the JSON array contains value, or 0 if it does not.

Parameters:

Return type: BOOLEAN

Example:

Check whether [1, 2, 3] contains 2:

SELECT json_array_contains('[1, 2, 3]', 2);

+-------------------------------------+
| json_array_contains('[1, 2, 3]', 2) |
+-------------------------------------+
|                1                    |
+-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

Returns the number of elements in a JSON array.

Parameters:

Return type: BIGINT

Example:

Get the length of [1, 2, 3]:

SELECT json_array_length('[1, 2, 3]');

+--------------------------------+
| json_array_length('[1, 2, 3]') |
+--------------------------------+
|                 3              |
+--------------------------------+

JSON_CONTAINS

JSON_CONTAINS checks whether a JSON document contains a given value. It has two forms:

• Standard form — works on any JSON data

• Index-optimized form — uses a JSON array index to avoid full table scans

json_contains(target, candidate[, json_path])

SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.a') as result;

+--------+
| result |
+--------+
|      1 |
+--------+

SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.b') as result;

+--------+
| result |
+--------+
|      0 |
+--------+

SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '{"d": 4}') as result;

+--------+
| result |
+--------+
|      0 |
+--------+

json_contains(json_path, cast('[candidate1, candidate2, ...]' as json))

SELECT json_contains(vj, cast('["CP-018673","CP-018671"]' AS json)) FROM json_test;

+------------------------------------------------------------+
|json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))|
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+

SELECT json_contains(vj, cast('["CP-018673",1,2]' AS json)) FROM json_test;

+------------------------------------------------------------+
|json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))|
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, json_path[, json_path, ...])

Checks whether a JSON document contains values at the specified paths.

• 'one': returns 1 if at least one of the paths exists in the document.

• 'all': returns 1 only if all paths exist in the document.

Parameters:

Return type: BOOLEAN

Examples:

Check whether the document contains at least one of $.a and $.e:

SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') AS result;

+--------+
| result |
+--------+
|      1 |
+--------+

Check whether the document contains both $.a and $.e:

SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') AS result;

+--------+
| result |
+--------+
|      0 |
+--------+

JSON_EXTRACT

json_extract(json, json_path)

Returns the value at json_path in a JSON document.

If a key contains special characters such as $ or ., use bracket notation: '$["key"]'. For example, to access the key $date, use '$["$date"]'.

Parameters:

Return type: JSON

Examples:

Extract the first element of the array [10, 20, [30, 40]]:

SELECT json_extract('[10, 20, [30, 40]]', '$[0]');

+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
|                     10                     |
+--------------------------------------------+

Extract the value of a key that contains a special character ($date):

SELECT JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]');

+---------------------------------------------------------+
|JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]') |
+---------------------------------------------------------+
|                       "12345"                           |
+---------------------------------------------------------+

JSON_KEYS

json_keys(json[, json_path])

Returns all keys of a JSON object. If json_path is specified, returns keys at that path. Without json_path, returns keys at the root ($).

The json parameter must be of the JSON type. Construct it with:

• JSON literal: json '{"a": 1}'

• CAST function: CAST('{"a": 1}' AS json)

Parameters:

Return type: JSON array

Examples:

Get all keys in the nested object at $.b:

SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json), '$.b');

+-----------------------------------------------------------+
| json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
+-----------------------------------------------------------+
|                           ["c"]                           |
+-----------------------------------------------------------+

Get all top-level keys:

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

+--------------------------------------------+
| JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
+--------------------------------------------+
|             ["a","b"]                      |
+--------------------------------------------+

JSON_OVERLAPS

json_overlaps(json, cast('[candidate1, candidate2, ...]' as json))

Checks whether a JSON document contains any of the specified elements. Use this function in WHERE clauses to filter rows where a JSON array column overlaps with a given set of values.

All candidate values must share the same data type (numeric or STRING).

Parameters:

Return type: VARCHAR

Examples:

Return all rows where column vj contains CP-018673:

SELECT * FROM json_test WHERE json_overlaps(vj, cast('["CP-018673"]' AS json));

+-----+----------------------------------------------------------------------------+
|  id |   vj                                                                       |
+-----+----------------------------------------------------------------------------+
|  2  | ["CP-018673", 1, false]                                                    |
+-----+----------------------------------------------------------------------------+
|  3  | ["CP-018673", 1, false, {"a": 1}]                                          |
+-----+----------------------------------------------------------------------------+
|  5  | ["CP-018673","CP-018671","CP-018672","CP-018670","CP-018669","CP-018668"]  |
+-----+----------------------------------------------------------------------------+

Return all rows where column vj contains any of 1, 2, or 3:

SELECT * FROM json_test WHERE json_overlaps(vj, cast('[1,2,3]' AS json));

+-----+-------------------------------------+
|  id |                 vj                  |
+-----+-------------------------------------+
|  1  | [1,2,3]                             |
+-----+-------------------------------------+
|  2  | ["CP-018673", 1, false]             |
+-----+-------------------------------------+
|  3  | ["CP-018673", 1, false, {"a": 1}]   |
+-----+-------------------------------------+

JSON_REMOVE

json_remove(json, json_path)
json_remove(json, array[json_path, json_path, ...])

Removes one or more elements from a JSON document and returns the updated document as a string. To remove multiple elements in one call, pass an array of paths.

Parameters:

Return type: VARCHAR

Examples:

Remove the element at $.glossary.GlossDiv:

SELECT json_remove(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }',
  '$.glossary.GlossDiv') a;

{"glossary":{"title":"example glossary"}}

Remove multiple elements at once using an array of paths:

SELECT json_remove(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }',
  array['$.glossary.title', '$.glossary.GlossDiv.title']) a;

{"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}

JSON_SET

json_set(json, json_path, value[, json_path, value] ...)

Inserts or updates one or more values in a JSON document and returns the updated document. Pass multiple json_path, value pairs to update several locations in one call.

Parameters:

Return type: JSON

Behavior:

• If json or json_path is NULL, returns NULL.

• If json is not a valid JSON document, or a json_path is not a valid path expression, an exception is thrown.

• If the path exists, its value is overwritten.

• If the path does not exist:

  • Points to a JSON object: the value is inserted as a new key.

  • Points to a JSON array index: if empty slots exist before the index, they are filled with NULL before the value is inserted; otherwise, the value is inserted directly.

  • Other cases: an exception is thrown.

Examples:

When json_path is NULL, the function returns NULL:

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');

+------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') |
+------------------------------------------------+
| null                                           |
+------------------------------------------------+

When a path points to an array element but is not a valid insertion point, the function returns an error:

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');

Failed to execute json_set() for json_path: $.b.c

Update an existing key ($.a) and insert a new key ($.c) in one call:

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

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

Insert into an array at index 4 (indices 2 and 3 are filled with NULL):

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b[4]', '[true, false]');

+----------------------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]') |
+----------------------------------------------------------------+
| {"a":1,"b":[2,3,null,null,"[true, false]"]}                    |
+----------------------------------------------------------------+

JSON_SIZE

json_size(json, json_path)

Returns the number of keys in a JSON object, or the number of elements in a JSON array, at the specified path.

Returns 0 if json_path does not point to a JSON object or JSON array.

Parameters:

Return type: BIGINT

Examples:

Get the size of the JSON object at $.x:

SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

+--------+
| result |
+--------+
|      2 |
+--------+

When the path points to a scalar value (not an object or array), the function returns 0:

SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

+--------+
| result |
+--------+
|      0 |
+--------+

JSON_UNQUOTE

json_unquote(json_value)

Removes the outer double quotes from json_value and unescapes recognized escape sequences. The input is not validated against JSON syntax — any VARCHAR input is processed as-is.

Supported escape sequences:

Parameters:

Return type: VARCHAR

Examples:

Remove the outer double quotes from "abc":

SELECT json_unquote('"abc"');

+-----------------------+
| json_unquote('"abc"') |
+-----------------------+
|          abc          |
+-----------------------+

Unescape a tab character and a Unicode code point:

SELECT json_unquote('"\\t\\u0032"');

+------------------------------+
| json_unquote('"\\t\\u0032"') |
+------------------------------+
|               2              |
+------------------------------+

\t is a tab and \u0032 is the character 2, so the result is a tab followed by 2.

JSON path syntax

All path-based functions use the following syntax to address values in a JSON document.

Patterns:

• $.keyName — access a key in a JSON object

• $.keyName.nestedKey — access a nested key

• $[n] — access the n-th element of a JSON array (n is a non-negative integer)

• $.keyName[n] — access the n-th element of a nested JSON array

Wildcards (* and **) are not supported. Paths such as '$.*', '$.hobbies[*]', '$.address.'`, and `'$.hobbies.' are invalid.

Examples using the following JSON document:

{
    "name": "Alice",
    "age": 25,
    "address": {
        "city": "Hangzhou",
        "zip": "10001"
    },
    "hobbies": ["reading", "swimming", "cycling"]
}

Troubleshooting

SELECT table_name, schema_name, status
FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK
ORDER BY create_time DESC
LIMIT 10;

What's next

• JSON — learn about the JSON data type in AnalyticDB for MySQL

• JSON indexes — create indexes on JSON objects or arrays to improve query performance