【MySQL】json_extract解析json
xixuefeng
MySQL, MySQL-DEV
2019-08-01 11:04:21
1,432 次浏览
json_extract
【MySQL】json_extract解析json已关闭评论
MySQL5.7
json串如下：
|
1 |
{"Data":{"List":[{"ID":"101010","NAME":"ZHANGSAN","IDCARD":"110101000001291"},{"ID":"201010","NAME":"LISI","IDCARD":"220101000001291"}]}} |
json_extract解析json
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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; +----------+------------+----------+--------+ | id1 | name1 | id2 | name2 | +----------+------------+----------+--------+ | "101010" | "ZHANGSAN" | "201010" | "LISI" | +----------+------------+----------+--------+ 1 row in set mysql> |
问题：解析后的字符串都包含双引号，解决此问题有如下两个方法
方法一：json_unquote()
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 set mysql> |
方法二：->>
|
1 2 3 4 5 6 7 8 9 10 |
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 set mysql> |