mysql操纵json函数

Dear 丶 2022-05-10 09:16 217阅读 0赞

原帖地址：[https://www.cnblogs.com/pfdltutu/p/9019444.html][https_www.cnblogs.com_pfdltutu_p_9019444.html]

create table t1(name json);   
insert into t1 values(’ \{  
“hello”: “song”,  
“num”: 111,  
“obj”: \{ “who”: “me”, “arr”: \[ 1, 2, “three” \], “more”:“hey” \},  
“bool”: true,  
“can”: false,  
“learning”: null,  
“chiness”: “中文”  
\}' );

mysql> select json_depth(name) from t1;

返回json文本的深度 输出结果----4；   
1 rows in set (0.02 sec)

mysql> select json_length(name) from t1;

返回json文本的长度 输出结果----9  
1 rows in set (0.01 sec)

mysql> select json_type(name) from t1;

返回json值得类型 输出结果----OBJECT   
1 rows in set (0.04 sec)

mysql> SELECT JSON_VALID(‘hello’) a, JSON_VALID(‘“hello”’) b;

判读是否是合法的json类型 返回结果：a:0 b:1  
1 row in set (0.00 sec)

mysql> select json_keys(name) as a from t1;

查看存储的json有哪些key 返回结果：array\[  
'a'=> \[“hello”, “link”, “can”, “num”, “bool”, “learning”, “notLink”, “obj”, “chiness”\],  
\]  
1 rows in set (0.05 sec)

mysql> select json_keys(name,’obj’) as a from t1;

查看obj有哪些key 返回结果：array\['a' =>\[“more”,“who”,“arr”\]\]  
5rowsinset(0.00sec)

mysql>select jsonsearch(name,′one′,′me′) as a from t1;

查看第一次出现的位置 返回结果：array\['“.obj.who”'\]

mysql> select json_search(name,’all’,’%aaaaa%’) as a from t1;

查看所有包含aaaaa的位置 返回结果：aaaaa所在的key json\_search(name,’all’,’%json%’)   
1rows in set (0.00 sec)

mysql> select json_extract(name,’link[0]’) as a from t1;

抽取值 返回结果：查找的key所对应的value 如：“http://jsonview.com  
1 rows in set (0.00 sec)

mysql> select json_extract(name,’obj[0].more[0]’) from t1;

抽取值 返回结果同上  
1 rows in set (0.00 sec)   
或者使用下面的方式

mysql> select name,name->’key2’ from t1;

\{“key1”: “value1”, “key2”: “value2”\} | “value2” |