mysql查询json数组里的数据_mysqljson数组查询

json数据格式如下

 [{"num": 10, "days": 1, "signDate": "2022-12-29", "signStatus": 1}, {"num": 20, "days": 2, "signDate": "2022-12-30", "signStatus": 1}, {"num": 30, "days": 3, "signDate": "2022-12-31", "signStatus": 0}, {"num": 40, "days": 4, "signDate": "2023-01-01", "signStatus": 0}, {"num": 50, "days": 5, "signDate": "2023-01-02", "signStatus": 0}, {"num": 60, "days": 6, "signDate": "2023-01-03", "signStatus": 0}, {"num": 70, "days": 7, "signDate": "2023-01-04", "signStatus": 0}]
1

SELECT ss.signStatus ,ss.signDate FROM table 
CROSS JOIN
JSON_TABLE(
sign_info,
'$[*]' COLUMNS( signStatus VARCHAR(100) PATH '$.signStatus', signDate VARCHAR(100) PATH '$.signDate') 
) AS ss where ss.signStatus = 1
1
2
3
4
5
6

sigin_json 是 table 中的json字段
signDate 和signStatus 为json对象中的两个属性，取出来变成signDate和signStatus 两列

多维数组的JSON格式

	 {"class": "三年一班", "students": [{"name": "张三", "address": "上海市浦东新区张江"},{"name": "李四", "address": "上海市浦东新区张江"}]},

1
2

SELECT s.id,s.name,ss.name,ss.address FROM school s
CROSS JOIN
JSON_TABLE(
json_info,
'$[*]' COLUMNS (NESTED PATH '$.students[*]' COLUMNS(name VARCHAR(100) PATH '$.name', address VARCHAR(100) PATH '$.address')) 
) AS ss
WHERE json_info IS NOT NULL;
1
2
3
4
5
6
7

大体思路就是把json数据转换成一个临时表 然后用json_table处理

注：mysql版本要大于8.0