• 描述:返回包含所有参数值的 JSON 数组(数组可为空)。
  • 示例:
    SELECT JSON_ARRAY(123, "polardb-x", NULL, TRUE);
    +------------------------------------------+
    | JSON_ARRAY(123, 'polardb-x', NULL, true) |
    +------------------------------------------+
    | [123,"polardb-x",null,true]              |
    +------------------------------------------+
  • JSON_OBJECT([key, val [, key, val] ...])

  • 描述:根据参数中键值对来创建一个 JSON 对象,如果 key 为 NULL 或参数个数不为 2 的倍数,将返回相应错误提示。
  • 示例:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x');
    +---------------------------------------------+
    | JSON_OBJECT('id', 123, 'name', 'polardb-x') |
    +---------------------------------------------+
    | {"name":"polardb-x","id":123}               |
    +---------------------------------------------+
  • JSON_QUOTE(string)

  • 描述:将 JSON 值用双引号括起来,并对特殊处理字符进行转义,返回 utf8mb4 编码的字符串结果。若参数为 NULL,返回值为 NULL。该函数通常用于生成一个合法的 JSON 字符串字面量。
  • 示例:
    SELECT JSON_QUOTE(null), JSON_QUOTE('"abc"');
    +------------------+---------------------+
    | JSON_QUOTE(NULL) | JSON_QUOTE('"abc"') |
    +------------------+---------------------+
    | NULL             | ""abc""             |
    +------------------+---------------------+
  • JSON_CONTAINS(target, candidate [, path])

  • 描述:判断给定的 candidate JSON 文档是否包含了 target JSON 文档;如果给定了 path 参数,则是判断 candidate 能否找到包含 target 的路径 path,结果返回 1 或 0。如果只关心 candidate 是否存在路径 path,可以使用 JSON_CONTAINS_PATH 函数。
  • 示例:
    SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
    SELECT JSON_CONTAINS(@json_doc, '123', '$.a');
    +----------------------------------------+
    | JSON_CONTAINS(@json_doc, '123', '$.a') |
    +----------------------------------------+
    |                                      1 |
    +----------------------------------------+
    SELECT JSON_CONTAINS(@json_doc, 'null', '$.b');
    +-----------------------------------------+
    | JSON_CONTAINS(@json_doc, 'null', '$.b') |
    +-----------------------------------------+
    |                                       1 |
    +-----------------------------------------+
    SELECT JSON_CONTAINS(@json_doc, '123', '$.b');
    +----------------------------------------+
    | JSON_CONTAINS(@json_doc, '123', '$.b') |
    +----------------------------------------+
    |                                      0 |
    +----------------------------------------+
    SELECT JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c');
    +-----------------------------------------------+
    | JSON_CONTAINS(@json_doc, '{"d": 456}', '$.c') |
    +-----------------------------------------------+
    |                                             1 |
    +-----------------------------------------------+
  • JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

  • 判断给定的 json_doc JSON 文档是否存在一条或多条路径 path,结果返回 1 或 0。参数 one_or_all 的取值规定如下:
  • 'one':只要 json_doc 存在参数中的至少一条路径,就返回 1,否则返回 0;
  • 'all':只有 json_doc 包含参数中的所有路径时,才返回 1,否则返回 0。
  • 示例:
    SET @json_doc = '{"a": 123, "b": null, "c": {"d": 456}}';
    SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e');
    +----------------------------------------------------+
    | JSON_CONTAINS_PATH(@json_doc, 'one', '$.a', '$.e') |
    +----------------------------------------------------+
    |                                                  1 |
    +----------------------------------------------------+
    SELECT JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e');
    +----------------------------------------------------+
    | JSON_CONTAINS_PATH(@json_doc, 'all', '$.a', '$.e') |
    +----------------------------------------------------+
    |                                                  0 |
    +----------------------------------------------------+
    SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d');
    +-----------------------------------------------+
    | JSON_CONTAINS_PATH(@json_doc, 'one', '$.c.d') |
    +-----------------------------------------------+
    |                                             1 |
    +-----------------------------------------------+
  • JSON_EXTRACT(json_doc, path[, path] ...)

  • 描述:从 JSON 文档中解析出路径 path 对应的部分。
  • 示例:
    SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]');
    +-------------------------------------------------+
    | JSON_EXTRACT('[123, 456, [789, 1000]]', '$[1]') |
    +-------------------------------------------------+
    | 456                                             |
    +-------------------------------------------------+
    SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]');
    +---------------------------------------------------------+
    | JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[1]') |
    +---------------------------------------------------------+
    | [123,456]                                               |
    +---------------------------------------------------------+
    SELECT JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]');
    +---------------------------------------------------------+
    | JSON_EXTRACT('[123, 456, [789, 1000]]', '$[0]', '$[2]') |
    +---------------------------------------------------------+
    | [123,[789,1000]]                                        |
    +---------------------------------------------------------+
  • column->path

  • 描述:->操作符提供了与 JSON_EXTRACT 函数相同的功能,返回 path 对应的列值。
  • 示例:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
    +-------------+
    | NAME        |
    +-------------+
    | "polardb-x" |
    +-------------+
  • column->>path

  • 描述:->>操作符在->操作符的基础上,对返回结果去除了双引号并反转义(参考 JSON_UNQUOTE 函数)。该操作符与以下两个函数组合等价:
  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE( column -> path )
  • 示例:
    SELECT JSON_OBJECT('id', 123, 'name', 'polardb-x')->"$.name" as NAME;
    +-------------+
    | NAME        |
    +-------------+
    | "polardb-x" |
    +-------------+
  • JSON_KEYS(json_doc[, path])

  • 以 JSON 数组的形式返回 json_doc 对象最外层的 key 值列表。
  • 示例:
    SELECT JSON_KEYS('{"a": 123, "b": {"c": 456}}');
    +------------------------------------------+
    | JSON_KEYS('{"a": 123, "b": {"c": 456}}') |
    +------------------------------------------+
    | ["a","b"]                                |
    +------------------------------------------+
  • JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char[, path] ...])

  • 返回给定字符串 search_str 在 json_doc 中的路径,未找到则返回 NULL。search_str 中支持包含如 LIKE 中使用的 % 和 _ 通配符作为模糊匹配。参数 escape_char 指定了转义字符,而参数 one_or_all 的取值规定如下:
  • 'one':返回第一个匹配的路径值(哪一条路径第一个匹配到是不确定的);
  • 'all':以数组形式返回所有路径值(数组元素顺序是不确定的)。
    说明 PolarDB-X 暂不支持**路径通配符。
  • 示例:
    SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';
    SELECT JSON_SEARCH(@json_doc, 'one', 'abc');
    +--------------------------------------+
    | JSON_SEARCH(@json_doc, 'one', 'abc') |
    +--------------------------------------+
    | "$[0]"                               |
    +--------------------------------------+
    SELECT JSON_SEARCH(@json_doc, 'all', 'abc');
    +--------------------------------------+
    | JSON_SEARCH(@json_doc, 'all', 'abc') |
    +--------------------------------------+
    | ["$[0]","$[2].k2"]                   |
    +--------------------------------------+
    SELECT JSON_SEARCH(@json_doc, 'all', 'xyz');
    +--------------------------------------+
    | JSON_SEARCH(@json_doc, 'all', 'xyz') |
    +--------------------------------------+
    | NULL                                 |
    +--------------------------------------+
    SELECT JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]');
    +----------------------------------------------------+
    | JSON_SEARCH(@json_doc, 'all', 'def', NULL, '$[*]') |
    +----------------------------------------------------+
    | "$[1][1]"                                          |
    +----------------------------------------------------+
    SELECT JSON_SEARCH(@json_doc, 'all', '%a%');
    +--------------------------------------+
    | JSON_SEARCH(@json_doc, 'all', '%a%') |
    +--------------------------------------+
    | ["$[0]","$[2].k2"]                   |
    +--------------------------------------+
  • JSON_APPEND(json_doc, path, val [, path, val] ...)

    即 JSON_ARRAY_APPEND 函数。
    说明 该函数在MySQL5.7中已过时,在MySQL8.0中被移除。

    JSON_ARRAY_APPEND(json_doc, path, val [, path, val] ...)

  • 描述:将 val 追加到 json_doc 指定 JSON 数组末尾,并返回修改后的 json_doc。当有多对(path, val)参数时,该函数会从左到右依次计算每一对参数,并将追加完前一对参数后的结果作为下一次计算的输入。
  • 示例:
    SET @json_doc = '{"a": 1, "b": [2, 3], "c": 4}';
    SELECT JSON_ARRAY_APPEND(@json_doc, '$.b', 'x');
    +------------------------------------------+
    | JSON_ARRAY_APPEND(@json_doc, '$.b', 'x') |
    +------------------------------------------+
    | {"a":1,"b":[2,3,"x"],"c":4}              |
    +------------------------------------------+
    SELECT JSON_ARRAY_APPEND(@json_doc, '$.c', 'y');
    +------------------------------------------+
    | JSON_ARRAY_APPEND(@json_doc, '$.c', 'y') |
    +------------------------------------------+
    | {"a":1,"b":[2,3],"c":[4,"y"]}            |
    +------------------------------------------+
  • JSON_ARRAY_INSERT(json_doc, path, val [, path, val] ...)

  • 描述:将 val 插入到 json_doc 中 JSON 数组的指定位置,并返回修改后的 json_doc。如果 val 插入位置超出了数组大小,则会追加为最后一个元素。当有多对(path, val)参数时,该函数会从左到右依次计算每一对参数,并将插入完前一对参数后的结果作为下一次计算的输入。
  • 示例:
    SET @json_doc = '["a", {"b": [1, 2]}, [3, 4]]';
    SELECT JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x');
    +-------------------------------------------+
    | JSON_ARRAY_INSERT(@json_doc, '$[1]', 'x') |
    +-------------------------------------------+
    | ["a","x",{"b":[1,2]},[3,4]]               |
    +-------------------------------------------+
    SELECT JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x');
    +--------------------------------------------+
    | JSON_ARRAY_INSERT(@json_doc, '$[10]', 'x') |
    +--------------------------------------------+
    | ["a",{"b":[1,2]},[3,4],"x"]                |
    +--------------------------------------------+
    SELECT JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x');
    +------------------------------------------------+
    | JSON_ARRAY_INSERT(@json_doc, '$[1].b[1]', 'x') |
    +------------------------------------------------+
    | ["a",{"b":[1,"x",2]},[3,4]]                    |
    +------------------------------------------------+
    SELECT JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y');
    +-----------------------------------------------------------+
    | JSON_ARRAY_INSERT(@json_doc, '$[0]', 'x', '$[3][1]', 'y') |
    +-----------------------------------------------------------+
    | ["x","a",{"b":[1,2]},[3,"y",4]]                           |
    +-----------------------------------------------------------+
  • JSON_INSERT(json_doc, path, val [, path, val] ...)

  • 描述:将 val 插入到 json_doc 的指定位置。当有多对(path, val)参数时,该函数会从左到右依次计算每一对参数,并将插入完前一对参数后的结果作为下一次计算的输入。插入 val 时的行为,将区分以下两种情况:
  • 如果指定 path 处本身已存在值,则会忽略当前这轮处理的(path, val)对,不会覆盖原本已存在的值;
  • 如果指定 path 处不存在值,则分为以下三种情况:
  • 如果指定 path 处是对象内不存在的成员,则直接在对象内添加对应的键值对;
  • 如果指定 path 处是超出数组下标的元素,则会将 val 添加到该数组末尾(标量值会自动包装为只含一个元素的数组);
  • 其他情况,则会忽略当前这轮处理的(path, val)对,不采取任何操作。
  • 请参考 JSON_REPLACE 函数、 JSON_SET 函数来比较三者用法的不同。

  • 示例:
    SET @json_doc = '{ "a": 1, "b": [2, 3]}';
    SELECT JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------------+
    | JSON_INSERT(@json_doc, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------------+
    | {"a":1,"b":[2,3],"c":"[true, false]"}                     |
    +-----------------------------------------------------------+
  • JSON_MERGE(json_doc, json_doc [, json_doc] ...)

    即 JSON_MERGE_PRESERVE 函数(注:该函数在 MySQL5.7 中已过时,在 MySQL8.0.3 中被移除)。

    JSON_MERGE_PATCH(json_doc, json_doc [, json_doc] ...)

  • 合并多个 JSON 文档,且会对相同的 key 进行去重。该函数将从左到右依次合并两个 json_doc(下称为 doc1 与 doc2),合并规则如下:
  • 如果 doc1 不是对象类型,则合并结果即为 doc2;
  • 如果 doc2 不是对象类型,则合并结果也为 doc2;
  • 如果 doc1 与 doc2 都是对象类型,则合并结果是包含了以下成员的对象:
  • key 只存在于 doc1、而不存在于 doc2 的所有 doc1 成员;
  • key 只存在于 doc2、而不存在于 doc1,且值不是 null 字面量的所有 doc2 成员;
  • 对于 key 既存在于 doc1、又存在于 doc2 且在 doc2 中值不为 null 字面量的成员,会将 key 对应的两个 value 再按上述规则进行递归合并。
  • 根据上述规则可知,如果有重复 key 则会保留 doc2 中对应的值。

  • 示例:
    SELECT JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}');
    +----------------------------------------------------------+
    | JSON_MERGE_PATCH('{"name": "polardb-x"}', '{"id": 123}') |
    +----------------------------------------------------------+
    | {"name":"polardb-x","id":123}                            |
    +----------------------------------------------------------+
    SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
    +--------------------------------------------------+
    | JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
    +--------------------------------------------------+
    | {"a":1}                                          |
    +--------------------------------------------------+
    SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }',
        -> '{ "a": 5, "d":6 }');
    +---------------------------------------------------------------------------------+
    | JSON_MERGE_PATCH('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
    +---------------------------------------------------------------------------------+
    | {"a":5,"b":2,"c":4,"d":6}                                                       |
    +---------------------------------------------------------------------------------+
  • JSON_MERGE_PRESERVE(json_doc, json_doc [, json_doc] ...)

    合并多个 JSON 文档,会保留所有的值而不会根据相同 key 去重。合并规则如下:

  • 两个数组的合并结果为一个数组;
  • 两个对象的合并结果为一个对象;
  • 标量类型值会自动包装为数组类型进行合并;
  • 一个对象和一个数组合并,则会把对象包装为含单个元素数组并进行合并。
  • 示例:(可以对比与 JSON_MERGE_PATCH 函数的不同)
    SELECT JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}');
    +-------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{"name": "polardb-x"}', '{"id": 123}') |
    +-------------------------------------------------------------+
    | {"name":"polardb-x","id":123}                               |
    +-------------------------------------------------------------+
    SELECT JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}');
    +-----------------------------------------------------+
    | JSON_MERGE_PRESERVE('{"a":1, "b":2}', '{"b":null}') |
    +-----------------------------------------------------+
    | {"a":1,"b":[2,null]}                                |
    +-----------------------------------------------------+
    SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }',
        ->  '{ "a": 5, "d":6 }');
    +------------------------------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{ "a": 1, "b":2 }', '{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
    +------------------------------------------------------------------------------------+
    | {"a":[1,3,5],"b":2,"c":4,"d":6}                                                    |
    +------------------------------------------------------------------------------------+
  • JSON_REMOVE(json_doc, path [, path] ...)

  • 描述:删除 json_doc 下指定路径的元素。当有多个 path 参数时,该函数会从左到右依次计算路径并删除元素,并将前一次删除后的结果作为下一次计算的输入。若指定路径不存在,不会报错。
  • 示例:
    SET @json_doc = '["a", ["b", "c"], "d"]';
    SELECT JSON_REMOVE(@json_doc, '$[1]');
    +--------------------------------+
    | JSON_REMOVE(@json_doc, '$[1]') |
    +--------------------------------+
    | ["a","d"]                      |
    +--------------------------------+
  • JSON_REPLACE(json_doc, path, val [, path, val] ...)

  • 描述:用 val 替换 json_doc 的指定位置的值,只会替换已存在的值。当有多对(path, val)参数时,该函数会从左到右依次计算每一对参数,并将计算完前一对参数后的结果作为下一次计算的输入。若指定路径不存在,不会报错。
  • 示例:
    SET @json_doc = '{ "a": 1, "b": [2, 3]}';
    SELECT JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]');
    +------------------------------------------------------------+
    | JSON_REPLACE(@json_doc, '$.a', 10, '$.c', '[true, false]') |
    +------------------------------------------------------------+
    | {"a":"10","b":[2,3]}                                       |
    +------------------------------------------------------------+
  • JSON_SET(json_doc, path, val [, path, val] ...)

  • 描述:在 json_doc 的指定位置插入或更新值 val。该函数会从左到右依次计算每一对参数,并将计算完前一对参数后的结果作为下一次计算的输入。将区分以下两种情况:
  • 如果指定 path 处本身已存在值,会用参数 val 的值覆盖原本已存在的值;
  • 如果指定 path 处不存在值,则分为以下三种情况:
  • 如果指定 path 处是对象内不存在的成员,则直接在对象内添加对应的键值对;
  • 如果指定 path 处是超出数组下标的元素,则会将 val 添加到该数组末尾(标量值会自动包装为只含一个元素的数组);
  • 其他情况,则会忽略当前这轮处理的(path, val)对,不采取任何操作。
  • 示例:
    SET @json_doc = '{ "a": 1, "b": [2, 3]}';
    SELECT JSON_SET(@json_doc, '$.a', 10, '$.c', '[true, false]');
    +-------------------------------------------------+
    | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
    +-------------------------------------------------+
    | {"a":10,"b":[2,3],"c":[true,false]}             |
    +-------------------------------------------------+
  • JSON_UNQUOTE(json_val)

  • 描述:去除 JSON 值外围的双引号,对转义字符进行反转义,返回 utf8mb4 编码的字符串结果。
  • 示例:
    SELECT JSON_UNQUOTE('"abc"');
    +-----------------------+
    | JSON_UNQUOTE('"abc"') |
    +-----------------------+
    | abc                   |
    +-----------------------+
    SELECT JSON_UNQUOTE('"a\\tbc"');
    +--------------------------+
    | JSON_UNQUOTE('"a\\tbc"') |
    +--------------------------+
    | a    bc                     |
    +--------------------------+
    SELECT JSON_UNQUOTE('"\\t\\u0032"');
    +------------------------------+
    | JSON_UNQUOTE('"\\t\\u0032"') |
    +------------------------------+
    |     2                           |
    +------------------------------+
  • JSON_DEPTH(json_doc)

    返回 json_doc 的最大深度,规则如下:
  • 空数组、空对象以及标量类型,深度均为 1;
  • 只有单个元素的数组深度为 1;
  • 对象中如果所有成员的 value 深度均为 1,则自身的深度为 2。
  • 示例:
    SELECT JSON_DEPTH('[10, {"a": 20}]');
    +-------------------------------+
    | JSON_DEPTH('[10, {"a": 20}]') |
    +-------------------------------+
    |                             3 |
    +-------------------------------+
  • JSON_LENGTH(json_doc [, path])

    返回 json_doc 的长度,规则如下:

  • 标量类型的长度为 1;
  • 数组类型的长度为数组元素个数;
  • 对象类型的长度为对象成员个数;
  • 结果不包含内部嵌套的数组或对象长度。
  • 示例:
    SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
    +-----------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
    +-----------------------------------------+
    |                                       2 |
    +-----------------------------------------+
  • JSON_TYPE(json_val)

  • 描述:返回参数 json_val 的 JSON 类型。
  • 示例:
    SET @json_obj = '{"a": [10, true]}';
    SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a'));
    +-------------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a')) |
    +-------------------------------------------+
    | ARRAY                                     |
    +-------------------------------------------+
    SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]'));
    +----------------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[0]')) |
    +----------------------------------------------+
    | INTEGER                                      |
    +----------------------------------------------+
    SELECT JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]'));
    +----------------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@json_obj, '$.a[1]')) |
    +----------------------------------------------+
    | BOOLEAN                                      |
    +----------------------------------------------+
  • JSON_VALID(val)

  • 描述:判断参数值 val 是否为合法的 JSON 格式。
  • 示例:
    SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
    +---------------------+-----------------------+
    | JSON_VALID('hello') | JSON_VALID('"hello"') |
    +---------------------+-----------------------+
    |                   0 |                     1 |
    +---------------------+-----------------------+
  • JSON_PRETTY(json_doc)

  • 描述:通过添加换行与缩进,以更直观的方式打印出 json_doc。
  • 示例:
    SET @json_doc = '["abc", [{"k1": 123}, "def"], {"k2": "abc"}, {"k3": null}]';
    SELECT JSON_PRETTY(@json_doc);
    +---------------------------------------------------------------------------+
    | JSON_PRETTY(@json_doc)                                                    |
    +---------------------------------------------------------------------------+
        "abc",
                "k1":123
            "def"
            "k2":"abc"
    +---------------------------------------------------------------------------+
  • JSON_STORAGE_SIZE(json_doc)

  • 描述:返回 json_doc 以二进制形式存储的字节大小。
  • 示例:
    SET @json_doc = '[999, "polardb-x", [1, 2, 3], 888.88]';
    SELECT JSON_STORAGE_SIZE(@json_doc) AS Size;
    +------+
    | Size |
    +------+
    |   48 |
    +------+