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

JSON_ARRAY_CONTAINS：判断JSON中是否包含value指定的值。
JSON_ARRAY_LENGTH：返回JSON数组的长度。
JSON_CONTAINS（3.1.5.0及以上版本）：判断指定Path中是否包含candidate的值；若未指定Path，则判断Target中是否包含candidate指定的值。
JSON_CONTAINS_PATH（3.1.5.0及以上版本）：判断JSON中是否包含Path列表中的路径。
JSON_EXTRACT：从JSON中返回json_path指定的值。
JSON_KEYS：若指定了json_path，表示获取JSON在指定路径下的所有键。若未指定json_path，表示获取根路径（即json_path='$'）下的所有键。
JSON_OVERLAPS（3.1.10.6及以上版本）：从指定JSON中返回包含candidate1、candidate2、candidate3等任意一个元素的值。
JSON_REMOVE（3.1.10.0及以上版本）：从json中移除json_path指定的元素，并返回一个新的字符串。您可以通过array[json_path,json_path,...]指定移除多个元素。
JSON_SIZE：从JSON中返回json_path指定JSON对象或JSON数组的大小。
JSON_SET（3.2.2.8及以上版本）：在json中插入或更新指定json_path的数据并返回更新后的json。
JSON_UNQUOTE（3.1.5.0及以上版本）：去除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索引

使用JSON Array索引

重要

仅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均为JSON数组，当Candidate的所有元素均包含于Target的某个元素中时，视为Target包含Candidate。
- 若target为ARRAY类型且candidate为非ARRAY类型，当Candidate包含于Target的某个元素中时，视为Target包含Candidate。
- 若target和candidate均为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 |
+--------+

重要

仅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[, json_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_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, cast('[candidate1,candidate2,candidate]' as json))

命令说明：返回指定JSON中包含candidate1、candidate2、candidate3等任意一个元素的数据。
输入值类型：candidate1,candidate2,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

重要

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

如何查看和升级集群内核版本，请参见查看和升级版本。

json_remove(json,json_path)
json_remove(json,array[json_path,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_SET

重要

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

如何查看和升级集群内核版本，请参见查看和升级版本。

json_set(json, json_path, value[, json_path, value] ...)

命令说明：在json中插入或更新指定json_path的数据并返回更新后的json。
- 如果json或json_path为null，则返回null。
- 如果json不符合JSON格式规范，或者任一json_path不是一个有效的路径表达式，则会抛出异常。
- 如果指定的json_path存在，则会使用value覆盖原有的数据。
- 如果指定的json_path在json中不存在：
  - json_path指向的是JSON对象，value会作为新数据添加到json_path指定的位置中。
  - json_path指向的是JSON数组，先判断指定json_path位置前是否有数据，若没有，则补充null之后再插入value的数据。反之，则直接插入value的数据。
  - 其他情况则会抛出异常。
输入值类型：
- json：VARCHAR 或JSON。
- json_path：VARCHAR。
- value：BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、VARCHAR、VARBINARY、DATE、DATETIME、TIMESTAMP、TIME。
返回值类型：JSON。

示例：

向json中插入数据，其中json_path为null。

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');

返回结果：

+------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') |
+------------------------------------------------+
| null                                           |
+------------------------------------------------+

向json中插入数据，其中json_path不是一个有效的路径表达式。

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');

返回结果：

Failed to execute json_set() for json_path: $.b.c

向json中插入数据，其中json_path1已存在，json_path2不存在且指向的是JSON对象。

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');

返回结果：

+-----------------------------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------------------+
| {"a":10,"b":[2,3],"c":"[true, false]"}                                |
+-----------------------------------------------------------------------+

向json中插入数据，指定的json_path不存在，且指向的是JSON数组。

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]');

返回结果：

+----------------------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]') |
+----------------------------------------------------------------+
| {"a":1,"b":[2,3,null,null,"[true, false]"]}                    |
+----------------------------------------------------------------+

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

附录：JSON Path语法说明

使用方法

使用$.keyName[.keyName]...访问JSON对象中的指定键。
使用$[nonNegativeInteger]访问JSON数组中的第n个元素，n为非负整数。
使用$.keyName[.keyName]...[nonNegativeInteger]访问JSON对象中嵌套的JSON数组的第n个元素，n为非负整数。

注意事项

AnalyticDB for MySQL JSON Path语法不支持通配符*和**。即不支持以下写法'$.*'、'$.hobbies[*]'、'$.address.**'以及'$.hobbies.**'。

示例

假设有以下JSON数据。

{
    "name": "Alice",
    "age": 25,
    "address": {
        "city": "Hangzhou",
        "zip": "10001"
    },
    "hobbies":["reading", "swimming", "cycling"]
}

示例说明	正确示例	错误示例

示例说明	正确示例	错误示例
访问键name的值	$.name	name
访问嵌套对象city键的值	$.address.city	$.address[0]
访问JSON数组hobbies的第1个元素	$.hobbies[0]	$.hobbies.[0]

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

JSON_ARRAY_CONTAINS

JSON_ARRAY_LENGTH

JSON_CONTAINS

JSON_CONTAINS_PATH

JSON_EXTRACT

JSON_KEYS

JSON_OVERLAPS

JSON_REMOVE

JSON_SIZE

JSON_SET

JSON_UNQUOTE

附录：JSON Path语法说明

相关文档