AnalyticDB:JSON functions

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

• JSON_ARRAY_LENGTH: returns the length of a JSON array.

• JSON_CONTAINS: determines whether the value specified by the candidate parameter is contained in the path specified by the json_path parameter or the JSON document specified by the target parameter.

• JSON_CONTAINS_PATH: determines whether a JSON document contains specific paths.

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

• JSON_KEYS: returns all keys of a JSON array in the path specified by the json_path parameter. If you do not specify the json_path parameter, this function returns all keys in the root path $, which is equivalent to setting the json_path parameter to $.

• JSON_OVERLAPS: returns data that contains values of one or more elements specified by the candidate1, candidate2, and candidate3 parameters in a JSON array.

• JSON_REMOVE: returns the result of removing one or more elements specified by the json_path parameter from the JSON document specified by the json parameter. You can use the array[json_path,json_path,...] parameter to remove multiple elements.

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

• JSON_UNQUOTE: returns the result of unquoting the value specified by the json_value parameter and changing the escape characters in the value.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Description: This function determines whether a JSON array contains the value specified by the value parameter.

• Data type of the input value: NUMERIC, STRING, or BOOLEAN for the value parameter.

• Data type of the return value: BOOLEAN.

• Example:

  • Determine whether the JSON array [1, 2, 3] contains an element of 2. Sample statement:

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

    Sample result:

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

JSON_ARRAY_LENGTH

json_array_length(json)

• Description: This function returns the length of a JSON array.

• Data type of the input value: STRING or JSON.

• Data type of the return value: BIGINT.

• Example:

  • Return the length of the JSON array [1, 2, 3]. Sample statement:

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

    Sample result:

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

JSON_CONTAINS

The JSON_CONTAINS function is used to determine whether a JSON path or document contains specific content. You can use JSON array indexes in queries to prevent scanning the data of the entire table or parsing the entire JSON document. This 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[, path,...])

• Description: This function determines whether a JSON document contains specific paths.

  • If one_or_all is set to 'one' and the JSON document contains one of the specified paths, 1 is returned. Otherwise, 0 is returned.

  • If one_or_all is set to 'all' and the JSON document contains all the specified paths, 1 is returned. Otherwise, 0 is returned.

• Data types of the input values: JSON for the json parameter, VARCHAR for the one_or_all parameter (valid values: one and all, which are case-insensitive), and PATH for the json_path parameter.

• Data type of the return value: BOOLEAN.

• Example:

  • Determine whether a JSON document contains at least one of the $.a and $.e paths. Sample statement:

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

    Sample result:

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

  • Determine whether a JSON document contains the $.a and $.e paths. Sample statement:

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

    Sample result:

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

JSON_EXTRACT

json_extract(json, json_path)

• Description: This function returns the value specified by the json_path parameter from a JSON array.

• Data type of the input value: STRING or JSON.

• Data type of the return value: JSON.

• Example:

  • Return the value of the $.1 path from the JSON array [10, 20, [30, 40]]. Sample statement:

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

    Sample result:

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

JSON_KEYS

json_keys(json, json_path)
json_keys(json)

• Description:

  • If you specify the json_path parameter, this function returns all keys of a JSON array in the specified path.

  • If you do not specify the json_path parameter, this function returns all keys in the root path $, which is equivalent to setting the json_path parameter to $.

• Data type of the input value: JSON.

  You can use one of the following methods to construct JSON data:

  • Directly use JSON data. Example: json '{"a": 1, "b": {"c": 30}}'.

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

• Data type of the return value: JSON ARRAY.

• Examples:

  • Return all keys in the $.b path. Sample statement:

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

    Sample result:

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

  • Return all keys in the root path. Sample statement:

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

    Sample result:

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

JSON_OVERLAPS

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

• Description: This function returns data that contains values of one or more elements specified by the candidate1, candidate2, and candidate3 parameters in a JSON array.

• Data type of the input value: NUMERIC or STRING for the candidate1, candidate2, and candidate3 parameters. Values of all parameters must be of the same data type.

• Data type of the return value: VARCHAR.

• Examples:

  • Return the data that contains CP-018673 in the specified vj JSON array.

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

    Sample result:

    +-----+----------------------------------------------------------------------------+
    |  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 that contains one or more elements among 1, 2, and 3 in the specified vj JSON array.

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

    Sample result:

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

• Description: This function returns the result of removing one or more elements specified by the json_path parameter from the JSON document specified by the json parameter. You can use the array[json_path,json_path,...] parameter to remove multiple elements.

• Data types of the input values: JSON VARCHAR for the json and json_path parameters.

• Data type of the return value: VARCHAR.

• Examples:

  • Return the result of removing the $.glossary.GlossDiv path from a JSON document. Sample statement:

      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;

    Sample result:

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

  • Return the result of removing the $.glossary.title and $.glossary.GlossDiv.title paths from a JSON document. Sample statement:

    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;

    Sample result:

    {"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: This function returns the size of the JSON object or array specified by the json_path parameter from a JSON array.

  Note

  If you do not use the json_path parameter to specify a JSON object or array, this function returns 0.

• Data type of the input value: STRING or JSON.

• Data type of the return value: BIGINT.

• Examples:

  • The json_path parameter specifies a JSON object. Sample statement:

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

    Sample result:

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

  • The json_path parameter does not specify a JSON object or array. Sample statement:

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

    Sample result:

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

JSON_UNQUOTE

json_unquote(json_value)

• Description: This function returns the result of unquoting the value specified by the json_value parameter and changing the escape characters in the value.

  AnalyticDB for MySQL does not determine the validity of 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 escape characters.

  Escape character	After escaping
  \"	Double quotation marks (")
  \b	Backspace key
  \f	Form feed
  \n	Line feed
  \r	Carriage return
  \t	Tab key
  \\	Backslash (\)
  \uXXXX	UTF-8 character

• Data type of the input value: VARCHAR.

• Data type of the return value: VARCHAR.

• Examples:

  • Return the result of unquoting the "abc" string. Sample statement:

    SELECT json_unquote('"abc"');

    Sample result:

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

  • Return the result of unquoting the "\\t\\u0032" string and changing the escape characters in the string. Sample statement:

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

    Sample result:

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