云原生数据仓库AnalyticDB：JSON函数

• JSON_ARRAY_CONTAINS：判断JSON中是否包含value指定的值。

• JSON_ARRAY_LENGTH：返回JSON数组的长度。

• JSON_CONTAINS：判断指定Path中是否包含candidate的值；若未指定Path，则判断Target中是否包含candidate指定的值。

• JSON_CONTAINS_PATH：判断JSON中是否包含Path列表中的路径。

• JSON_EXTRACT：从JSON中返回json_path指定的值。

• JSON_KEYS：若指定了json_path，表示获取JSON在指定路径下的所有键。若未指定json_path，表示获取根路径（即json_path='$'）下的所有键。

• JSON_OVERLAPS：从指定JSON中返回包含candidate1、candidate2、candidate3等任意一个元素的值。

• JSON_REMOVE：从json中移除json_path指定的元素，并返回一个新的字符串。您可以通过array[json_path,json_path,...]指定移除多个元素。

• JSON_SIZE：从JSON中返回json_path指定JSON对象或JSON数组的大小。

• JSON_UNQUOTE：去除json_value的双引号并将json_value中的部分转义符进行转义后，返回处理结果。

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• 命令说明：判断JSON数组中是否包含value指定的值。

• 输入值类型：value可以是数值、字符串类型或BOOLEAN类型。

• 返回值类型：BOOLEAN。

• 示例：

  • 判断JSON数组 [1, 2, 3]中是否包含值为2的元素，语句如下：

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

    返回结果如下：

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

JSON_ARRAY_LENGTH

json_array_length(json)

• 命令说明：返回JSON数组的长度。

• 输入值类型：字符串类型或JSON类型。

• 返回值类型：BIGINT。

• 示例：

  • 返回JSON数组[1, 2, 3]的长度，语句如下：

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

    返回结果如下：

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

JSON_CONTAINS

JSON_CONTAINS函数用于判断指定JSON中是否包含特定内容，您可以在查询数据时使用JSON Array索引，避免扫描全表数据或对整个JSON文档进行解析，提升数据查询效率。

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

• 命令说明：判断JSON中是否包含Path对应的值。

  • 当one_or_all为'one'，JSON中包含所有Path中的其中之一时，返回1，否则返回0。

  • 当one_or_all为'all'，JSON中包含所有Path路径时，返回1，否则返回0。

• 输入值类型：json为JSON类型，one_or_all为VARCHAR类型（为'one'或者'all'，不区分大小写），json_path为Path路径。

• 返回值类型：BOOLEAN。

• 示例：

  • 判断JSON中是否包含$.a和$.e至少一个的路径，语句如下：

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

    返回结果如下：

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

  • 判断JSON中是否包含$.a和$.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)

• 命令说明：从JSON中返回json_path指定的值。

• 输入值类型：字符串类型或JSON类型。

• 返回值类型：JSON。

• 示例：

  • 返回数组[10, 20, [30, 40]]中路径为$.1的值，语句如下：

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

    返回结果如下：

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

JSON_KEYS

json_keys(json, json_path)
json_keys(json)

• 命令说明

  • 若指定了json_path，表示获取JSON在指定路径下的所有键。

  • 若未指定json_path，表示获取根路径（即json_path='$'）下的所有键。

• 输入值类型：仅支持输入JSON类型的参数。

  您可以通过如下方式构造JSON数据：

  • 直接使用JSON数据。例如json '{"a": 1, "b": {"c": 30}}'。

  • 通过CAST函数将字符串进行显式转换为JSON数据。例如CAST('{"a": 1, "b": {"c": 30}}' AS json)。

• 返回值类型：JSON ARRAY。

• 示例：

  • 返回$.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"]                           |
    +-----------------------------------------------------------+

  • 返回根路径下的所有键，语句如下：

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

    返回结果如下：

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

JSON_OVERLAPS

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

• 命令说明：返回指定JSON中包含candidate1、candidate2、candidate3等任意一个元素的数据。

• 输入值类型：candidate1candidate2,candidate3,......为数值类型或字符串类型，且多个值的类型需相同。

• 返回值类型：VARCHAR。

• 示例：

  • 返回指定JSON列vj中包含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"]  |
    +-----+----------------------------------------------------------------------------+

  • 返回指定JSON列vj中包含1、2、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中移除json_path指定的元素，并返回一个新的字符串。您可以通过array[json_path,json_path,...]指定移除多个元素。

• 输入值类型：json为JSON格式的VARCHAR类型。json_path为JSON格式的VARCHAR类型。

• 返回值类型：VARCHAR。

• 示例

  • 移除路径为$.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"}}

  • 移除路径为$.glossary.title和$.glossary.GlossDiv.title的部分，并返回修改后的字符串，语句如下：

    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_SIZE

json_size(json, json_path)

• 命令说明：从JSON中返回json_path指定JSON对象或JSON数组的大小。

  说明

  若json_path指向的不是JSON对象或者JSON数组时，返回0。

• 输入值类型：字符串类型或JSON类型。

• 返回值类型：BIGINT。

• 示例：

  • json_path指向的是JSON对象，语句如下：

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

    返回结果如下：

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

  • json_path指向的不是JSON对象或者JSON数组，语句如下：

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

    返回结果如下：

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

JSON_UNQUOTE

json_unquote(json_value)

• 命令说明：去除json_value的双引号并将其中的部分转义符进行转义后，返回处理结果。

  AnalyticDB for MySQL不会判断json_value的合法性，即无论json_value是否符合JSON语法都会按上述逻辑进行处理。

  支持的转义符如下表。

  转义前	转义后
  \"	双引号（"）。
  \b	退格键。
  \f	换页符。
  \n	换行符。
  \r	回车符。
  \t	Tab键。
  \\	反斜线（\）。
  \uXXXX	UTF-8字符表示。

• 输入值类型：VARCHAR。

• 返回值类型：VARCHAR。

• 示例：

  • 返回去除引号后的字符串abc，语句如下：

    SELECT json_unquote('"abc"');

    返回结果如下：

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

  • 返回去除引号并解析后的字符串，语句如下：

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

    返回结果如下：

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