全部产品
Search
文档中心

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

更新时间:Jul 15, 2024

本文介绍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中返回包含candidate1candidate2candidate3等任意一个元素的值。

  • 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。

    规则如下:

    • targetcandidate均为PRIMITIVE类型(即NUMBER,BOOLEAN,STRING,NULL),当二者相等时,视为Target包含Candidate。

    • targetcandidate均为ARRAY类型的JSON,当Candidate的所有元素均包含于Target的某个元素中时,视为Target包含Candidate。

    • target为ARRAY类型且candidate为非ARRAY类型,当Candidate包含于Target的某个元素中时,视为Target包含Candidate。

    • targetcandidate均为OBJECT类型的JSON,当Candidate中的每个Key都包含于Target的Key中,且Candidate的Key对应的Value包含于Target中该Key对应的Value时,视为Target包含Candidate。

  • 输入值类型:targetcandidate为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中是否同时包含candidate1candidate2candidate3等元素。

  • 输入值类型:candidate1,candidate2,candidate3,......为数值类型或字符串类型,且多个值的类型需相同。

  • 返回值类型:VARCHAR。

  • 示例:

    • 判断指定JSON列vj中是否包含CP-018673CP-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-01867312

      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中包含candidate1candidate2candidate3等任意一个元素的数据。

  • 输入值类型: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中包含123任意一个元素的数据。

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