本文介绍AnalyticDB MySQL版集群支持的JSON函数。

  • JSON _ARRAY_CONTAINS：判断JSON中是否包含value指定的值。
  • JSON_ARRAY_LENGTH：返回JSON数组的长度。
  • JSON _EXTRACT：从JSON中返回jsonpath指定的值。
  • JSON_SIZE：返回JSON的大小。
  • JSON_KEYS：获取JSON在指定路径下的所有键值。
  • JSON_UNQUOTE：去除json_value的双引号并将json_value中的部分转义符进行转义后，返回处理结果。
  • JSON_CONTAINS：判断指定Path中是否包含candidate的值；若未指定Path，则判断Target中是否包含candidate指定的值。
  • JSON_CONTAINS_PATH：判断JSON中是否包含Path列表中的路径。

JSON _ARRAY_CONTAINS

json_array_contains(json, value)
  • 命令说明：判断JSON数组中是否包含value指定的值。
  • 输入值类型：value可以是数值、字符串类型或BOOLEAN类型。
  • 返回值类型：BOOLEAN。
  • 示例： 
    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。
  • 示例： 
    SELECT json_array_length('[1, 2, 3]');
    返回结果如下：
    +--------------------------------+
| json_array_length('[1, 2, 3]') |
+--------------------------------+
|                              3 |
+--------------------------------+

JSON _EXTRACT

重要
  • JSON_EXTRACT函数的返回值，同JSON类型的列一样，均不支持ORDER BY。
  • JSON_EXTRACT函数与JSON_UNQUOTE函数连用时，需要先使用CAST AS VARCHAR将JSON_EXTRACT函数的返回值转换为VARCHAR类型才能作为JSON_UNQUOTE函数的入参。 
json_extract(json, jsonpath)
  • 命令说明：从JSON中返回jsonpath指定的值。
  • 输入值类型：字符串类型或JSON类型。
  • 返回值类型：JSON。
  • 示例：
    SELECT json_extract('[10, 20, [30, 40]]', '$.1');
    返回结果如下：
    +-------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$.1') |
+-------------------------------------------+
| 20                                        |
+-------------------------------------------+

JSON_SIZE

json_size(json, jsonpath)
  • 命令说明：从JSON中返回jsonpath指定JSON对象或JSON数组的大小。
    说明 若jsonpath指向的不是JSON对象或者JSON数组时，返回0。
  • 输入值类型：字符串类型或JSON类型。
  • 返回值类型：BIGINT。
  • 示例：
    • jsonpath指向的是JSON对象，语句如下：
      SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      2 |
+--------+
    • jsonpath指向的不是JSON对象或者JSON数组，语句如下：
      SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      0 |
+--------+

JSON_KEYS

json_keys(json, jsonpath)
json_keys(json)
  • 命令说明
    • 若指定了jsonpath，表示获取JSON在指定路径下的所有键。
    • 若未指定jsonpath，表示获取根路径（即jsonpath='$'）下的所有键。
  • 输入值类型：仅支持输入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_UNQUOTE

json_unquote(json_value)
说明 AnalyticDB MySQL版集群的内核版本需为V3.1.5.0或以上版本才支持该函数。

如何查看集群版本，请参见查看版本。

如需升级版本，请提交工单联系技术支持。

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

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

    支持的转义符如下表。
    转义前 转义后
    \" 双引号（"）。
    \b 退格键。
    \f 换页符。
    \n 换行符。
    \r 回车符。
    \t Tab键。
    \\ 反斜线（\）。
    \uXXXX UTF-8字符表示。
  • 输入值类型：VARCHAR。
  • 返回值类型：VARCHAR。
  • 示例：
    • 语句如下：
      SELECT json_unquote('"abc"');
      返回结果如下：
      +-----------------------+
| json_unquote('"abc"') |
+-----------------------+
| abc                   |
+-----------------------+
    • 语句如下：
      SELECT json_unquote('"\\t\\u0032"');
      返回结果如下：
      +------------------------------+
| json_unquote('"\\t\\u0032"') |
+------------------------------+
|    2                         |
+------------------------------+

JSON_CONTAINS

json_contains(target, candidate[, path])
说明 AnalyticDB MySQL版集群的内核版本需为V3.1.5.0或以上版本才支持该函数。

如何查看集群版本，请参见查看版本。

如需升级版本，请提交工单联系技术支持。

  • 命令说明：
    • 若指定了path，则判断指定Path中是否包含candidate的值。包含返回1，不包含返回0。
    • 若未指定path，则判断Target中是否包含candidate指定的值。包含返回1，不包含返回0。
    规则如下：
    • 若target和candidate均为PRIMITIVE类型（即NUMBER，BOOLEAN，STRING，NULL)，当二者相等时，视为Target包含Candidate。
    • 若target和candidate均为ARRAY类型的JSON，当Candidate的所有元素均包含于Target的某个元素中时，视为Target包含Candidate。
    • 若target为ARRAY类型且candidate为非ARRAY类型，当Candidate包含于Target的某个元素中时，视为Target包含Candidate。
    • 若target和candidate均为OBJECT类型的JSON，当Candidate中的每个Key都包含于Target的Key中，且Candidate的Key对应的Value包含于Target中该Key对应的Value时，视为Target包含Candidate。
  • 输入值类型：target和candidate为JSON类型，path为jsonpath。
  • 返回值类型：BOOLEAN。
  • 示例：
    • 语句如下：
      select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.a') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      1 |
+--------+
    • 语句如下：
      select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.b') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      0 |
+--------+
    • 语句如下：
      select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '{"d": 4}') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      0 |
+--------+

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, path[, path ...])
说明 AnalyticDB MySQL版集群的内核版本需为V3.1.5.0或以上版本才支持该函数。

如何查看集群版本，请参见查看版本。

如需升级版本，请提交工单联系技术支持。

  • 命令说明：判断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'，不区分大小写），path为Jsonpath。
  • 返回值类型：BOOLEAN。
  • 示例：
    • 语句如下：
      select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      1 |
+--------+
    • 语句如下：
      select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') as result;
      返回结果如下：
      +--------+
| result |
+--------+
|      0 |
+--------+