AnalyticDB:JSON functions

• JSON_ARRAY_CONTAINS: Checks whether a JSON array contains the specified value.

• JSON_ARRAY_LENGTH: Returns the length of a JSON array.

• JSON_CONTAINS (for versions 3.1.5.0 and later): Checks whether the specified path contains the candidate value. If no path is specified, this function checks whether the target contains the candidate value.

• JSON_CONTAINS_PATH (for versions 3.1.5.0 and later): Checks whether a JSON document contains any or all of the specified paths.

• JSON_EXTRACT: Extracts data from a JSON document at the specified json_path.

• JSON_KEYS: If json_path is specified, this function returns all keys from the specified path of a JSON document. If json_path is not specified, this function returns all keys from the root path (json_path='$').

• JSON_OVERLAPS (for versions 3.1.10.6 and later): Checks whether a JSON document contains any of the specified elements, such as candidate1, candidate2, or candidate3.

• JSON_REMOVE (for versions 3.1.10.0 and later): Removes the element at the specified json_path from a json document and returns the modified string. You can specify multiple elements to remove using `array[json_path,json_path,...]`.

• JSON_SIZE: Returns the size of the JSON object or array at the specified json_path.

• JSON_SET (for versions 3.2.2.8 and later): Inserts or updates data in a json document at the specified json_path and returns the updated json document.

• JSON_UNQUOTE (versions 3.2.2.11 and later): Removes the double quotation marks from json_value, unescapes specific escape characters in json_value, and returns the resulting value.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Description: Checks whether a JSON array contains the specified value.

• Input value type: value can be a numeric type, string, or BOOLEAN.

• Return value type: BOOLEAN.

• Example:

  • Check whether the JSON array [1, 2, 3] contains the element `2`. The statement is as follows:

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

    The following result is returned:

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

JSON_ARRAY_LENGTH

json_array_length(json)

• Description: Returns the length of a JSON array.

• Input value type: String or JSON.

• Return value type: BIGINT.

• Example:

  • Return the length of the JSON array [1, 2, 3]. The statement is as follows:

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

    The following result is returned:

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

JSON_CONTAINS

The `JSON_CONTAINS` function checks whether a specified JSON document contains a specific value. You can use a JSON array index in your queries to avoid full table scans or parsing of the entire JSON document, which improves query efficiency.

json_contains(target, candidate[, json_path])

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

JSON_CONTAINS_PATH

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

• Command description: Checks if the specified path exists in the JSON object.

  • If one_or_all is set to 'one', the function returns `1` if the JSON document contains any of the specified paths. Otherwise, it returns `0`.

  • If one_or_all is set to 'all', the function returns `1` if the JSON document contains all of the specified paths. Otherwise, it returns `0`.

• Input value type: json is of the JSON type. one_or_all is of the VARCHAR type and can be 'one' or 'all' (case-insensitive). json_path is a path expression.

• Return value type: BOOLEAN.

• Examples:

  • Check whether the JSON document contains at least one of the paths $.a and $.e. The statement is as follows:

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

    The following result is returned:

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

  • Check whether the JSON document contains both of the paths $.a and $.e. The statement is as follows:

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

    The following result is returned:

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

JSON_EXTRACT

json_extract(json, json_path)

• Description: Extracts a value from a JSON document at the specified json_path. If a key in the json document contains special characters, such as $ or ., the json_path format must be '$["Key"]'.

  For example, if the key is $data, `json_path` must be '$["$data"]'.

• Input value type: String or JSON.

• Return value type: JSON.

• Examples:

  • Return the value at path `$[0]` from the array [10, 20, [30, 40]]. The statement is as follows:

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

    The following result is returned:

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

  • Return the value of the path `$date` from {"id":"1","$date":"12345"}. The statement is as follows:

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

    The following result is returned:

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

JSON_KEYS

json_keys(json[, json_path])

• Description

  • If json_path is specified, this function returns all keys from the specified path of the JSON document.

  • If json_path is not specified, this function returns all keys from the root path (json_path='$').

• Input value type: Only parameters of the JSON type are supported.

  You can construct JSON data in the following ways:

  • Use JSON data directly. For example, json '{"a": 1, "b": {"c": 30}}'.

  • Explicitly cast a string to JSON data using the CAST function. For example, CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Return value type: JSON ARRAY.

• Examples:

  • Return all keys from the path $.b. The statement is as follows:

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

    The following result is returned:

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

  • Return all keys from the root path. The statement is as follows:

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

    The following result is returned:

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

JSON_OVERLAPS

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

• Description: Checks whether the specified JSON document contains any of the specified elements, such as candidate1, candidate2, or candidate3.

• Data types of the input values: candidate1,candidate2,candidate3,... can be of the numeric type or string type, and all values must have the same data type.

• Return value type: VARCHAR.

• Examples:

  • Return data from the specified JSON column vj that contains CP-018673.

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

    The following result is returned:

    +-----+----------------------------------------------------------------------------+
    |  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 data from the specified JSON column vj that contains any of the elements 1, 2, or 3.

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

    The following result is returned:

    +-----+-------------------------------------+
    |  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,...])

• Description: Removes the element at the specified json_path from a json document and returns the modified string. You can specify multiple elements to remove using `array[json_path,json_path,...]`.

• Input value type: json is a VARCHAR string in JSON format. json_path is a VARCHAR string in JSON format.

• Return value type: VARCHAR.

• Examples

  • Remove the element at the path $.glossary.GlossDiv and return the modified string. The statement is as follows:

      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;

    The following result is returned:

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

  • Remove the elements at the paths $.glossary.title and $.glossary.GlossDiv.title and return the modified string. The statement is as follows:

    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;

    The following result is returned:

    {"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_SIZE

json_size(json, json_path)

• Description: Returns the size of a JSON object or array at the specified json_path.

  Note

  If json_path does not point to a JSON object or array, this function returns `0`.

• Input value type: String or JSON.

• Return value type: BIGINT.

• Examples:

  • json_path points to a JSON object. The statement is as follows:

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

    The following result is returned:

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

  • json_path does not point to a JSON object or array. The statement is as follows:

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

    The following result is returned:

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

JSON_SET

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

• Description: Inserts or updates data in a json document at the specified json_path and returns the updated json document.

  • If json or json_path is null, the function returns null.

  • If the json document is not in a valid JSON format, or if any json_path is not a valid path expression, an exception is thrown.

  • If the specified json_path exists, its value is overwritten with value.

  • If the specified json_path does not exist in the json document:

    • If json_path points to a JSON object, value is added as a new element at the location specified by json_path.

    • If json_path points to a JSON array, this function checks whether data exists at the position before the specified json_path. If no data exists, `null` values are added to fill the gap before value is inserted. Otherwise, value is inserted directly.

    • In other cases, an exception is thrown.

• Input value types:

  • json: VARCHAR or JSON.

  • json_path: VARCHAR.

  • value: BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, VARBINARY, DATE, DATETIME, TIMESTAMP, or TIME.

• Return value type: JSON.

• Examples:

  • Insert data into a json document where json_path is null.

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

    Result:

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

  • Insert data into a json document where json_path is not a valid path expression.

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

    Result:

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

  • Insert data into a json document where json_path1 exists, and json_path2 does not exist and points to a JSON object.

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

    Result:

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

  • Insert data into a json document where the specified json_path does not exist and points to a JSON array.

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

    Result:

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

JSON_UNQUOTE

json_unquote(json_value)

• This command removes double quotation marks from json_value, escapes certain escape characters, and returns the resulting value.

  AnalyticDB for MySQL does not validate json_value. This function processes the value based on the logic described, regardless of whether json_value conforms to the JSON syntax.

  The supported escape characters are listed in the following table.

  Before unescaping	After unescaping
  \"	Double quotation mark (").
  \b	Backspace.
  \f	Form feed.
  \n	Line feed.
  \r	Carriage return.
  \t	Tab.
  \\	Backslash (\).
  \uXXXX	UTF-8 character representation.

• Input value type: VARCHAR.

• Return value type: VARCHAR.

• Examples:

  • Return the unquoted string abc. The statement is as follows:

    SELECT json_unquote('"abc"');

    The following result is returned:

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

  • The following statement returns the unquoted and parsed string:

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

    The result is as follows:

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

Appendix: JSON Path syntax

Usage

• Use $.keyName[.keyName]... to access a specified key in a JSON object.

• Use $[nonNegativeInteger] to access the Nth element in a JSON array, where n is a non-negative integer.

• Use $.keyName[.keyName]...[nonNegativeInteger] to access the Nth element of a JSON array that is nested in a JSON object, where n is a non-negative integer.

Notes

The JSON Path syntax in AnalyticDB for MySQL does not support the * and ** wildcard characters. This means that expressions such as '$.*', '$.hobbies[*]', '$.address.**', and '$.hobbies.**' are not supported.

Examples

Assume that you have the following JSON data.

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

FAQ

References

• JSON: Describes the JSON data type.

• JSON indexes: Describes how to create indexes for JSON objects and arrays.