添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
千杯不醉的树叶  ·  如何提取数据库中的汉字 | PingCode智库·  1 周前    · 
傻傻的佛珠  ·  sql not like 多条件 - CSDN文库·  1 周前    · 
发财的煎饼果子  ·  MindMaster超链接功能怎么使用 - 筋斗云·  3 月前    · 
文质彬彬的凉面  ·  《新大话西游2》2016全民PK季_该你出手 ...·  1 年前    · 
才高八斗的西红柿  ·  Getting the backtrace ...·  1 年前    · 
大力的西瓜  ·  Swing弹窗、组件、标签、面板、下拉框，文 ...·  1 年前    · 
腼腆的卡布奇诺  ·  《穿靴子的猫2》开启预售-新华网·  1 年前    · 
link管理  ›  ADBMySQL集群支持的JSON函数 - 云原生数据仓库AnalyticDB - 阿里云
返回值 数据仓库 字符串函数 select函数
https://www.alibabacloud.com/help/zh/analyticdb/analyticdb-for-mysql/developer-reference/json-functions
近视的包子
1 月前

本文介绍 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              |
+------------------------------+

 
推荐文章
千杯不醉的树叶  ·  如何提取数据库中的汉字 | PingCode智库
1 周前
傻傻的佛珠  ·  sql not like 多条件 - CSDN文库
1 周前
发财的煎饼果子  ·  MindMaster超链接功能怎么使用 - 筋斗云
3 月前
文质彬彬的凉面  ·  《新大话西游2》2016全民PK季_该你出手了_《新大话西游2》官网
1 年前
才高八斗的西红柿  ·  Getting the backtrace for all the threads in GDB - Bramante's Blog
1 年前
大力的西瓜  ·  Swing弹窗、组件、标签、面板、下拉框，文本框JFrame - 爱罗翔的张三 - 博客园
1 年前
腼腆的卡布奇诺  ·  《穿靴子的猫2》开启预售-新华网
1 年前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台