All Products
Search

This Product

    AnalyticDB for MySQL:JSON functions

    Document Center

    AnalyticDB for MySQL:JSON functions

    Last Updated:Apr 17, 2024

    This topic describes the JSON functions that are supported by AnalyticDB for MySQL.

    • 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_EXTRACT: returns the value specified by the json_path parameter from a JSON array.

    • JSON_SIZE: returns the size of the JSON object or array 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 json_path='$'.

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

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

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

    • 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_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 _EXTRACT

    Important

    • Similar to JSON columns, the return values of the JSON_EXTRACT function do not support the ORDER BY clause.

    • When you use the JSON_EXTRACT function together with the JSON_UNQUOTE function, you must invoke the CAST AS VARCHAR function to convert the return values of the JSON_EXTRACT function into the VARCHAR type before you can use the return values as input parameters of the JSON_UNQUOTE function. 

    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_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_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 json_path='$'.

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

    json_unquote(json_value)
    Important

    Only AnalyticDB for MySQL clusters of V3.1.5.0 or later support this function.

    • 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 mark (")

      \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                         |
+------------------------------+

    JSON_CONTAINS

    json_contains(target, candidate[, json_path])
    Important

    Only AnalyticDB for MySQL clusters of V3.1.5.0 or later support this function.

    • Description:

      • If you specify the json_path parameter, this function determines whether the value specified by the candidate parameter is contained in the path specified by the json_path parameter. A value of 1 specifies true, and a value of 0 specifies false.

      • If you do not specify the json_path parameter, this function determines whether the value specified by the candidate parameter is contained in the JSON document specified by the target parameter. A value of 1 specifies true, and a value of 0 specifies false.

      The following rules apply:

      • If the target and candidate parameters are of a primitive type, such as NUMBER, BOOLEAN, STRING, or NULL, and the two parameters are set to the same value, the value specified by the candidate parameter is considered to be contained in the JSON document specified by the target parameter.

      • If the target and candidate parameters are of the JSON ARRAY type and all elements of the candidate parameter are contained in an element of the target parameter, the value specified by the candidate parameter is considered to be contained in the JSON document specified by the target parameter.

      • If the target parameter is of the ARRAY type, the candidate parameter is not of the ARRAY type, and the value specified by the candidate parameter is contained in an element of the target parameter, the value specified by the candidate parameter is considered to be contained in the JSON document specified by the target parameter.

      • If the target and candidate parameters are of the JSON OBJECT type, each key of the candidate parameter is contained in the key of the target parameter, and each key value of the candidate parameter is contained in the corresponding key value of the target parameter, the value specified by the candidate parameter is considered to be contained in the JSON document specified by the target parameter.

    • Data types of the input values: JSON for the target and candidate parameters; JSONPATH for the json_path parameter.

    • Data type of the return value: BOOLEAN.

    • Examples:

      • Determine whether a value of 1 is contained in the $.a path. Sample statement:

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

        Sample result:

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

      • Determine whether a value of 1 is contained in the $.b path. Sample statement:

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

        Sample result:

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

      • Determine whether {"d": 4} is contained in the specified target parameter. Sample statement:

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

        Sample result:

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

    JSON_CONTAINS_PATH

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

    Only AnalyticDB for MySQL clusters of V3.1.5.0 or later support this function.

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

      • If you set the one_or_all parameter to one and the JSON document contains one of the paths specified by the json_path parameter, this function returns 1. Otherwise, this function returns 0.

      • If you set the one_or_all parameter to all and the JSON document contains all paths specified by the json_path parameter, this function returns 1. Otherwise, this function returns 0.

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

    • Data type of the return value: BOOLEAN.

    • Examples:

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

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

    Only AnalyticDB for MySQL clusters of V3.1.10.0 or later support the JSON_REMOVE function.

    • 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"}}}}}