本文介绍了PolarDB-X支持的JSON函数。
概览
PolarDB-X目前支持mysql 5.7的所有json函数,可分为以下五大类:
JSON_ARRAY([val [, val] ...])
- 描述:返回包含所有参数值的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 | +------+