mysqljson_extract⽤法,【MySQL】json_extract解析json MySQL5.7
json串如下：
{"Data":{"List":[{"ID":"101010","NAME":"ZHANGSAN","IDCARD":"110101000001291"}, {"ID":"201010","NAME":"LISI","IDCARD":"220101000001291"}]}}
送给孩子的一首歌json_extract解析json
mysql> SET @json = '{"Data":{"List":[{"ID":"101010","NAME":"ZHANGSAN","IDCARD":"110101000001291"}, {"ID":"201010","NAME":"LISI","IDCARD":"220101000001291"}]}}';
Query OK, 0 rows affected
mysql>
mysql> SELECT json_extract(@json,'$.Data.List[0].ID') as id1,
json_extract(@json,'$.Data.List[0].NAME') as name1,
json_extract(@json,'$.Data.List[1].ID') as id2,
json_extract(@json,'$.Data.List[1].NAME') as name2;
+----------+------------+----------+--------+
win10关机| id1 | name1 | id2 | name2 |
+----------+------------+----------+--------+
| "101010" | "ZHANGSAN" | "201010" | "LISI" |
+----------+------------+----------+--------+一碗白米饭
1 row in t
mysql>
问题：解析后的字符串都包含双引号，解决此问题有如下两个⽅法
⽅法⼀：json_unquote()
mysql> SET @json = '{"Data":{"List":[{"ID":"101010","NAME":"ZHANGSAN","IDCARD":"110101000001291"}, {"ID":"201010","NAME":"LISI","IDCARD":"220101000001291"}]}}';
口蘑的营养价值与功效SELECT json_extract(@json,'$.Data.List[0].ID') as strid,json_unquote(json_extract(@json,'$.Data.List[0].ID')) as intid;
Query OK, 0 rows affected
+----------+--------+
| strid | intid |冰糖泡
秋天的植物+----------+--------+
| "101010" | 101010 |
+----------+--------+
1 row in t
mysql>
⽅法⼆：->>
mysql> SELECT json_extract(myjson,'$.Data.List[0].ID') AS strid,myjson->>'$.Data.List[0].ID' as intid FROM
(SELECT '{"Data":{"List":[{"ID":"101010","NAME":"ZHANGSAN","IDCARD":"110101000001291"}, {"ID":"201010","NAME":"LISI","IDCARD":"220101000001291"}]}}' as myjson) t;
+----------+--------+
学包饺子作文| strid | intid |
+----------+--------+
| "101010" | 101010 |
+----------+--------+
1 row in t
收件箱
mysql>
