Mysql解析json字符串/数组

一、Mysql解析json字符串/数组

1 Mysql解析json字符串 

解决方法：JSON_EXTRACT(原字段，'$.json字段名')

执行SQL：

 SELECT JSON_EXTRACT( t.result,'$.row'),

 JSON_EXTRACT( t.result,'$.value'),

 JSON_EXTRACT( t.result,'$.criteria')

from t.test  t

 查询结果：

   

 2 mysql解析json数组

示例：

SELECT JSON_EXTRACT(
JSON_EXTRACT( t.msg,'$.rows'), "$[0].row") as row,
JSON_EXTRACT( JSON_EXTRACT( t.msg,'$.rows') , "$[0].value") as value,
JSON_EXTRACT(JSON_EXTRACT( t.msg,'$.rows'), "$[0].criteria") as criteria,
JSON_EXTRACT(JSON_EXTRACT( t.msg,'$.rows'), "$[0].id") as id
FROM t.test t

解析后数据：

   $[0]： 0代表数组下标 ，取第一条数据，注意，解析后的数据带双引号，可使用replace函数替换

 

oracle 解析JSON字符串：

参考：https://blog.csdn.net/qiruiduni/article/details/14123539

 

二、mysql 类型转化 json方法

1 cast(expr as type) 转化任意类型expr 为指定的类型

2 convert(expr,type), 同 cast(expr as type)

3 convert(expr using transcoding_name) 在不同地字符集间转化

4 binary expr  select binary 'a'='A';

5 extractvalue() 从xml字符串中使用xpath提取值

6 updatexml()返回被替换的xml片段

7 位运算 & | << >> ^

8 加密解密

9 current_user,current_user() 返回当有客户端验证用户

select user(); select current_user(); select * from mysql.user;

select session_user(); select system_user();

drop_user; rename_user

10 select database() 返回当前数据库

select schema();

11 select fund_rows() 返回查询行数

12 select last_insert_id()返回上一次插入的自增列值

13 select created table md5_tbl ;显示创建表的脚本

14 select row_count()返回影响的行数

15 select version();

 16 json_array([val[,val]...]) 返回包含这些值的json数组

select json_array(1,"a",null,true,curtime());

17 json_object([key,val[,key,val]...]) 返回包含这键值对的json对象 ，如果key是null或参数数量是奇数 发生出错

select json_object('id',87,'name','carrot');

18 cast(value as json)转货为json类型

19 json_quote(string) 作为json值 使用双引号引用一个字符串 ，并转方特殊字符

select json_quote('null'),json_quote('"null"');

20 json_contains(target,candidate[,path]) 返回 1 或 0 指出是否candidate json文档包含在target json文档 或者如果有path参数是否candidate在指定的target文档的path中存在

如果任一参数是null 或者path参数没有指出target文档中的一部分，则返回null,

如果target 或者 candidate不是有效的json文档 或者如果path参数不是一个有效的路径 报错。

只检查数据是否在路径中存在可以用：json_contains_path()来代替

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

set @j2="1";

select json_contains(@j,@j2,'$.a');

select json_contains(@j,@j2,'$.b');

set @j3='{"d":4}';

select json_contains(@j,@j3,'$.c');

21json_contains_path(json_doc,one or all,path[,path]...) 返回0或1 指示一个json文档是否包含给定路径下的数据

如果任何一个参数是null返回null,如果json_doc参数不是有效的json文档 或任一路径不是有效的路径表达式 或者 one_or_all 不是 one 或 all，出错。

one ：1 如果至少一个路径存在于文档中，否则 0

all：1 如果所有路径存在于文档中，否则 0

set @j='{"a":1,"b":2,"c":{"d":4}}'; select json_contains_path(@j,'one','$.a','$.e'); select json_contains_path(@j,'all','$.a','$.e');

22 json_extract(json_doc,path[,path]...)返回从json文档中按 path取到的部分数据。如果任一参数是null或没有找到path中的数据则返回null.如果json-doc.path 不是有效的数据，则出错

select json_extract('[10,20,[30,40]]','$[1]'); select json_extract('[10,20,[30,40]]','$[1]','$[0]');

select json_extract('[10],[20],[30,40]]','$[2][*]');

23 column->path 同 json_extract()

select c,json_extract(c,"$.id"),g from jemp where json_extract(c,"$.id")>1 order by json_extract(c,"$.name");

select c,c->"$.id",g from jemp where c->"$.id">1 order by c->"$.name";

update jemp set n=1 where c->"$.id"="4";

delete from jemp where c->"$.id"="4";

select a->"$[4]" from tj10 where a->"$[4][1]" is not null;

24 json_unquote(json_extract9column,path)) json_unquote(column->path) column->>path 返回不带双引号的数据

select c->'$.name' as name from jemp where g>2 ;

select json_unquote(c->'$.name') as name from jemp where g>2

select c->> '$.name' as name from jemp where g>2

25 json_keys(json_doc[,path]) 以json数组方式，返回顶层键值。如果有path参数，返回path参数中的顶层键值

select json_keys('{"a":1,"b":{"c":30}}'); select json_keys('{"a":1,"b":{"c":30}}','$.b');

26 json_search(json_doc,one_or_all,search_str(,escape_cahr[,path]...])

从json_doc 返回给定的字符 search_str 对应的部分数据

set @j='["abc",[{"k":"10"},"def"],{"x":"abc"},{"y":"bcd"}]';

select json_search(@j,'one','abc');

select json_search(@j,'all','abc');

select json_search(@j,'all','10');

 26 json_array_append(json_doc,path,val[,path,val]...) 添加值到json文档的指定的数组中，并返回结果值

如果 path 指示一个范围或对象值，则添加到path对象后成为一个数组

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

select json_array_append(@j,'$[1]',1); select json_array_append(@j,'$[0]',2);select json_array_append(@j,'$[1][0]',3);

set @j='{"a":1,"b":[2,3],"c":4}'; select json_array_append(@j,'$.b','x'); select json_array_append(@j,'$','z');

27 json_array_insert(json_doc,path,val[,path,val]...):更新json文档，插入到文档中的数组中返回修改后的文档，如果 path指定一个数组中的元素，插入到这个元素的位置，其他数据往右移。

set @j='["a",{"b":[1,2]},[3,4]]'; select json_array_insert(@j,'$[1]','x'); select json_array_insert(@j,'$[100]','x');

select json_array_insert(@j,'$[1].b[0]','x'); select json_array_insert(@j,'$[0]','x','$[2][1]','y');select json_array_insert(@j,'$[0]','x','$[2].b[1]','y');

插入一个值后，后面的值要按插入值后的序列取位置

 28 json_insert(json_doc,path,val[,path,val]...) 插入数据到json_doc并返回结果

如果在路径下存在值不重写存在 的值

如果元素不存在则插入数据，如果指定位置大于数组，则数组利用这个新值扩大。如果存在的值不是数组，则自动组成一个数组。

set @j='{"a":1,"b":[2,3]}'; select json_insert(@j,'$.a',10,'$.c','[true,false]'); $.a 存在则不插入数据 true/false 没有转为json数组

select josn_insert(@j,'$.c',cast('[true,false]' as json)); 转为json

28 json_merge_patch(json_doc,json_doc[,json_doc]...) 合并多个json文档并返回没有重复键的合并后的结果

如果只有一个参数则出错

如果第一参数不是一个对象，则作为空对象与第二个参数合并

如果第二个参数不是一个对象，则合并结果是第二个参数（不是null值的）

如果参数都是对象则合并规则：第一个对象中在第二个对象中不存在的键，第二个对象中在第一个中不存在的键，在2个对象中都存在的元素则取第二个对象中的值(不是null值的),

select json_merge_patch('[1,2]','[true,false]'); 如果第二个参数不是对象合并结果是第二个参数值

select json_merge_patch('{"name":"x"}','{"id":44}');

select json_merge_patch('1','true');如果第一个不是对象则取第二个值

select json_merge_patch('[1,2]','{"id":44}'); 第一个不是对象则按空对象合并

select json_merge_patch('{"a":1,"b":2}','{"a":3,"c":4}'); 2个键相同则取第2个键值

select json_merge_patch('{"a":1,"b":2}','{"b":null}'); 如果有相同值则取第二个参数中的数据如果在第二个参数中是空值则移除值

select json_merge_patch('{"a":{"x":1}}','{"a":{"y":2}}'); 递归合并

json_merge_patch() 与 json_merge_Preserve() 比较

json_merge_patch() 移除第一个参数中键值与第二个参数相同的对象，取第二个参数中不为null的值

json_merge_pathc()如果第二个参数中有相同元素键与第一个参数元素相同，则用第二个参数中的值替换第一个参数中的值,

json_merge_preserve()则追加第二个参数中的值到第一个参数中

set @x='{"a":1,"b":2}',@y='{"a":3,"c":4}',@z='{"a":5,"d":6}';

select json_merge_patch(@x,@y,@z) as patch,json_merge_preserve(@x,@y,@z) as preserve;

json_merge_preserve():合并2个相邻的数组成本一个；合并相邻的对象成一个；常量做为数组合并为一个数组；一个数组和一个对象相邻则自动包装对象为一个数组合并为一个数组。

select json_merge_preserve('[1,2]','[true,false]');2个 数组合并为一个数组

select json_merge_preserve('1','true');作为数组 合并为一个数组

select json_merge_preserve('{"name":"x"}','{"id":44}'); 2个对象合并为一个对象

select json_merge_preserve('[1,2]','{"id":44}'); 一个数组一个对象---对象做为数组合并为一个数组

29 json_remove(json_doc,path[,path]...) 从json_doc移除数据并返回结果

set @j='["a",["b","c"],"d"]'; select json_remove(@j,'$[1]');

30 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]');

 31 json_set(json_doc,path,val[,path,val]...) 插入或更新json_doc的数据，并返回结果

如果 一个元素不存在于json_doc中，这个元素被添加到对象中

如果一个位置放在已存在的数组的最后，则数组添加这个新值。如果存在的值不是数组，则被做为数组与新值一起组成数组

json_set() 替换存在的值并添加不存在的值；

json_insert()插入值不替换存在的值

json_replace() 替换存在的值

set @j='{"a":1,"b":[2,3]}'; select json_set(@j,'$.a',10,'$.c','[true,false]');

select json_insert(@j,'$.a',10,'$.c','[true,false]');

select json_replace(@j,'$.a',10,'$.c','[true,false]');

32json_depth(json_doc) 返回json_doc最大深度

空数组/空对象、深度为1的值，一个仅包含一个元素 且深度为1非空数组 或一个仅包含一个对象值并深度为1的非空对象 深度为 2.其他情况 下深度都大于2

select json_depth('{}'),json_depth('[]'),json_depth('true'); 

select json_depth('[10,20]'),json_depth('[[],{}]');

select json_depth('[10,{"a":20}]');

33 json_length(json_doc[,path]) 返回json_doc 或path参数指写的文档的长度

常量的长度是1

一个数组的长度是这个数组元素的个数

一个对象的长度是这个对象中成员的个数

不计算嵌套的长度

select json_length('[1,2,{"a":3}]');

select json_length('{"a":1,"b":{"c":30}}');

select json_length('{"a":1,"b":{"c":30}}','$.b');

34 json_type(json_val) 返回json值的类型

set @j='{"a":[10,true]}';

select json_type(@j);

select json_type(json_extract(@j,'$.a'));

select json_type(json_extract(@j,'$.a[0]'));

35 json_valid(val) 返回 0 或1 指示 是否是有效的json，如果参数是null返回null

select json_valid('{"a":1}'); select json_valid('hello'); select json_valid('"hello"');

36 json_pretty(json_val) 格式化显示json文档

select json_pretty('["a",1,{"key1":"value1"},"5","77",{"key2":["value3","valueX","valueY"]},"j","2"]');

 37 json_storage_size(json_val) 返回json_val存储字符数

select json_storage_size('["a",1,{"key1":"value1"},"5","77",{"key2":["value3","valueX","valueY"]},"j","2"]');

select json_storage_size('"a "') 计算空格 标点 占字符数