JSON函数

JSON创建函数

• JSON_ARRAY()：创建JSON数组

SELECT JSON_ARRAY(1,"abc",NULL,TRUE,CURTIME());
-- [1,"abc",null,true,"09:05:53.000000"]

• JSON_OBJECT()：创建JSON对象

SELECT JSON_OBJECT('id',87,'name','carrot');
-- {"id":87,"name":"carrot"}

• GaussDB(for MySQL)支持JSON数据类型。存储JSON数据时，JSON数据类型有明显优势
  • 数据库会自动对存储在JSON列中的JSON文件进行有效性验证，无效的文档会报错；
  • 最佳存储格式：存储在JSON列中的JSON文档会被转换为允许快速访问的内部格式；

JSON搜索函数

• JSON_CONTAINS(targe,candidate[,path])：判断是否包含特定的JSON文档，返回值为1或1。如果有参数为NULL或path指定的数据不存在，则返回NULL。

SET @j={"a":1,"b":2,"c":{"d":4}};
SET @j2='1';

SELECT JSON_CONTAINS(@j,@j2,'$.a');
-- 1

SELECT JSON_CONTAINS(@j,@j2,'$.b');
-- 0

• JSON_CONTAINS_PATH(json_doc,one_or_all,path[,path] …)：判断给定path/paths中是否包含JSON文档，返回值为0或1。

SET @j='{"a":1,"b":2,"c":{"d":4}}';

SELECT JSON_CONTAINS_PATH(@j,'one','$.a','$.e');
-- 1

SELECT JSON_CONTAINS_PATH(@j,'all','$.a','$.e');
-- 0

SELECT JSON_CONTAINS_PATH(@j,'one','$.c.d');
-- 1

• one:如果至少一个path指定的值存在，则函数返回1，否则返回0；

• all：如果所有path指定的值均存在，则函数返回1，否则返回0；

• 1表明至少一个path中存在 JSON 文档，0表明所有paths中均存在 JSON 文档；

• JSON_EXTRACT(json_doc,path[,path] …)：从JSON文档中返回特定路径的内容。

SELECT JSON_EXTEACT('[10,20,[30,40]]','$[1]');
-- 20

SELECT JSON_EXTRACT('[10,20,[30,40]]','$[1]','$[0]');
-- [20,10]

SELECT JSON_EXTRACT('{"a":1,"b":2,"c":{"d":4}}','$.c');
-- {"d":4}

• JSON_KEYS(json_doc[,path])：返回JSON对象的KEY值，返回一个数组；
• JSON_OVERLAPS(json_doc,json_doc2)：比较两个JSON文档，如果它们具有相同的键值或数组元素，则返回TRUE(1)，否则返回FALS(0)；
• JSON_SEARCH(json_doc,one_or_all,search_str[,escape_charr[,path]…])：搜索JSON文档中search_str所在的路径
• JSON_VALUE(json_doc,path)：返回JSON文档中特定路径的值。
• ……

JSON修改函数

• JSON_ARRAY_APPEND(json_doc,path,val[,path,val]…)：将值追加到JSON文档中指定数组的末尾并返回结果；

SET @j='["a",["b","c"],"d"]';

SELECT JSON_ARRAY_APPEND(@j,'$[1]',1);
-- ["a",["b","c",1],"d"]

SELECT JSON_ARRAY_APPEND(@j,'$[1][0]',3);
-- ["a",[["b",3],"c"],"d"]

• JSON_ARRAY_INSERT('json_doc,path,val[,path,val]…)：更新JSON文档，向文档中插入数组并返回修改后的文档。

SET @j='["a",{"b":[1,2]},[3,4]]';

SELECT JSON_ARRAY_INSERT(@j,'$[1]','x');
-- ["a","x",{"b":[1,2]},[3,4]]

SELECT JSON_ARRAY_INSERT(@j,'$[2][1]','y');
-- ["a",{"b":[1,2]},[3,"y",4]]

• 采用自左到右的顺序进行插入，执行插入操作后，其元素位置将发生变化，也将影响后续插入数据的位置定义；

• 如果path指定的数据非json array元素，则忽略此val；

• 如果指定的元素下标超过json array的长度，则在尾部插入；

• JSON_REMOVE(json_doc,path[,path]…)：从JSON文档删除数据并返回结果。

SET @j='["a",["b","c"],"d"]';

SELECT JSON_REMOVE(@j,'$[1]') ;
-- ["a","d"]

• JSON_REPLACE(json_doc,path,val[,path,val]…)：替换JSON文档中已存在的值并返回结果。

SET @j='{"a":1,"b":[2,3]}';

SELECT JSON_REPLACE(@j,'$.a',10,'$.c','[true,false]');
-- {"a":10,"b":[2,3]}

• JSON_INSERT()：若已存在值则忽略，若不存在则插入新值；
• JSON_REPLACE()：只替换已存在的值；
• 如果参数为NULL，则函数返回NULL；

JSON属性函数

• JSON_DEPTH(json_doc)：返回JSON文档最大深度。

SELECT JSON_DEPTH('[10,20]'),JSON_DEPTH('[[],{}]');
-- 2    2

• JSON_LENGTH(json_doc[,path])：返回JSON文档的长度，若指定了路径，则返回指定路径元素长度。

SELECT JSON_LENGTH('{"a":1,"b":{"c":30}}');
-- 2

• JSON_TYPE(json_val)：返回JSON值类型。
• JSON_LENGTH(json_doc[,path])函数
  • 标题的长度为1；
  • 数组的长度是数组所包含元素的数量；
  • 对象的长度是对象所包含元素的数量；
  • 嵌套数组或对象不参与长度计算；