ADBMySQL集群支持的JSON函数_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

本文介绍AnalyticDB for MySQL集群支持的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索引

重要

仅3.1.5.0及以上内核版本的集群支持该语法。

如何查看集群内核版本，请参见如何查看实例版本信息。如需升级内核版本，请联系技术支持。

json_contains(target, candidate[, json_path])

命令说明：
- 若指定了json_path，则判断指定Path中是否包含candidate的值。包含返回1，不包含返回0。
- 若未指定json_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类型，json_path为JSONPATH类型。
返回值类型：BOOLEAN。

示例：

判断$.a的路径下是否包含值1，语句如下：

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

返回结果如下：

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

判断$.b的路径下是否包含值1，语句如下：

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

返回结果如下：

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

判断{"d": 4}是否包含在Target中，语句如下：

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

返回结果如下：

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

使用JSON Array索引

重要

仅3.1.10.6及以上内核版本的集群支持该语法。
指定的JSON列需创建JSON Array索引。详情请参见创建JSON Array索引。
您可以在SQL查询语句前增加EXPLAIN，查看SQL的执行计划，若执行计划中无ScanFilterProject算子，则表明该查询成功利用JSON Array索引，反之，则未利用JSON Array索引。

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

命令说明：判断指定JSON中是否同时包含candidate1、candidate2、candidate3等元素。
输入值类型：candidate1,candidate2,candidate3,......为数值类型或字符串类型，且多个值的类型需相同。
返回值类型：VARCHAR。

示例：

判断指定JSON列vj中是否包含CP-018673和CP-018671。

SELECT  json_contains(vj, cast('["CP-018673","CP-018671"]' AS json)) FROM json_test;

返回结果如下：

+------------------------------------------------------------+
|json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))|                                                                    |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+

判断指定JSON列vj中是否包含CP-018673、1、2。

SELECT json_contains(vj, cast('["CP-018673",1,2]' AS json)) FROM json_test;

返回结果如下：

+------------------------------------------------------------+
|json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))|                                                                    |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+
|                    0                                       |
+------------------------------------------------------------+
|                    1                                       |
+------------------------------------------------------------+

JSON_CONTAINS_PATH

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

重要

仅3.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'，不区分大小写），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类型的列一样，均不支持ORDER BY。
JSON_EXTRACT函数与JSON_UNQUOTE函数连用时，需要先使用CAST AS VARCHAR将JSON_EXTRACT函数的返回值转换为VARCHAR类型才能作为JSON_UNQUOTE函数的入参。

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

重要

仅3.1.10.6及以上内核版本的集群支持该语法。
指定的JSON列需创建JSON Array索引。详情请参见创建JSON Array索引。
您可以在SQL查询语句前增加EXPLAIN，查看SQL的执行计划，若执行计划中无ScanFilterProject算子，则表明该查询成功利用JSON Array索引，反之，则未利用JSON Array索引。

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

重要

仅3.1.10.0及以上内核版本的集群支持使用JSON_REMOVE函数。

如何查看集群内核版本，请参见如何查看实例版本信息。如需升级内核版本，请联系技术支持。

命令说明：从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)

重要

仅3.1.5.0及以上内核版本的集群支持使用该函数。

如何查看集群内核版本，请参见如何查看实例版本信息。如需升级内核版本，请联系技术支持。

命令说明：去除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              |
+------------------------------+

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