AnalyticDB:JSON functions

• JSON_ARRAY_CONTAINS: checks whether a JSON array contains the value specified by the value parameter.

• JSON_ARRAY_LENGTH: returns the length of a JSON array.

• JSON_CONTAINS (Version 3.1.5.0 or later): checks whether the specified path contains the value of candidate. If a path is not specified, this function checks whether the target contains the value specified by candidate.

• JSON_CONTAINS_PATH (Version 3.1.5.0 or later): checks whether a JSON document contains one or all of the specified paths.

• JSON_EXTRACT: returns the value specified by the json_path parameter from a JSON document.

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

• JSON_OVERLAPS (Version 3.1.10.6 or later): checks whether a JSON document contains any of the specified candidate elements, such as candidate1, candidate2, or candidate3.

• JSON_REMOVE (Version 3.1.10.0 or later): removes the element specified by json_path from a json document and returns the new string. You can specify multiple paths in an array to remove multiple elements.

• JSON_SIZE: returns the size of the JSON object or array specified by the json_path parameter.

• JSON_SET (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 (Version 3.1.5.0 or later): removes the double quotes from json_value, unescapes specified escape characters in json_value, and returns the result.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

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

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

• Return value type: BOOLEAN.

• Example:

  • Checks whether the JSON array [1, 2, 3] contains an element with the value 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:

  • Returns 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 JSON document contains specific content. You can use JSON array indexes in queries to avoid full table scans or parsing the entire JSON document. This improves query performance.

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

• Description: Checks whether a JSON document contains a value at a specified path.

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

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

• Input value types: The json parameter is of the JSON type. The one_or_all parameter is of the VARCHAR type. Valid values are 'one' or 'all', which are case-insensitive. The json_path parameter is a path.

• Return value type: BOOLEAN.

• Examples:

  • Checks whether a 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 |
    +--------+

  • Checks whether a JSON document contains both the $.a and $.e paths. 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: Returns the value specified by json_path from a JSON document. If a key in json contains special characters, such as $ or ., the format of json_path must be '$["Key"]'.

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

• Input value type: STRING or JSON.

• Return value type: JSON.

• Examples:

  • Returns 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                    |
    +-------------------------------------------+

  • Returns the value of the `$date` path 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 JSON object at that path.

  • If json_path is not specified, this function returns all keys from the root object, which is equivalent to 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. Example: json '{"a": 1, "b": {"c": 30}}'.

  • Use the CAST function to explicitly convert a string to JSON data. Example: CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Return value type: JSON array.

• Examples:

  • Returns all keys in the $.b path. 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"]                           |
    +-----------------------------------------------------------+

  • Returns all keys in 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 a JSON document contains any of the specified elements, such as candidate1, candidate2, and candidate3.

• Input value types: The candidate1, candidate2, candidate3, ... parameters accept numeric or string values. All values must be of the same data type.

• Return value type: VARCHAR.

• Examples:

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

    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"]  |
    +-----+----------------------------------------------------------------------------+

  • Returns data that contains any of the elements 1, 2, or 3 from the specified JSON column vj.

    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 one or more elements specified by json_path from a json document and returns the new string. You can specify multiple paths in an array, such as `array[json_path, json_path, ...]` to remove multiple elements.

• Input value types: The json parameter is a VARCHAR string in JSON format. The json_path parameter is a VARCHAR string in JSON format.

• Return value type: VARCHAR.

• Examples

  • Removes the part at the path $.glossary.GlossDiv and returns 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"}}

  • Removes the parts at the paths $.glossary.title and $.glossary.GlossDiv.title and returns 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 the JSON object or JSON array that is specified by json_path.

  Note

  This function returns 0 if json_path does not point to a JSON object or a JSON array.

• Input value type: STRING or JSON.

• Return value type: BIGINT.

• Examples:

  • The 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 |
    +--------+

  • The json_path does not point to a JSON object or a JSON 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 a specified json_path and returns the updated json document.

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

  • An exception is thrown if json is not a valid JSON document, or if any json_path is not a valid path expression.

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

  • If the specified json_path does not exist in json:

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

    • If json_path points to a JSON array, this function checks whether there are empty slots before the position specified by json_path. If empty slots exist, they are filled with `null` values before the value is inserted. Otherwise, the value is inserted directly.

    • An exception is thrown in other cases.

• 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:

  • Inserts 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                                           |
    +------------------------------------------------+

  • Inserts 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

  • Inserts data into a json document. The path json_path1 exists, but 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]"}                                |
    +-----------------------------------------------------------------------+

  • Inserts data into a json document. 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)

• Description: Unquotes json_value by removing the outer double quotes and unescaping specific characters within the value, and then returns the result.

  AnalyticDB for MySQL does not validate the json_value parameter. The json_value parameter is processed based on the preceding logic, regardless of whether the parameter conforms to the JSON syntax.

  The following table describes the supported escape characters.

  Before escaping	After escaping
  \"	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:

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

    SELECT json_unquote('"abc"');

    The following result is returned:

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

  • Returns the unquoted and parsed string. The statement is as follows:

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

    The following result is returned:

    +------------------------------+
    | 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 n-th element of a JSON array, where n is a non-negative integer.

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

Notes

The JSON Path syntax in AnalyticDB for MySQL does not support the wildcard characters * and **. For example, the expressions '$.*', '$.hobbies[*]', '$.address.**', and '$.hobbies.**' are not supported.

Example

Assume that you have the following JSON data.

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

FAQ

References

• JSON: learn about the JSON type.

• JSON indexes: learn how to create indexes for JSON objects or JSON arrays.