参考:https://blog.csdn.net/qq_21187515/article/details/90760337
-
值为json数组
project 表
name data
合同 [{“id”:“1”,“status”:“UNCHECKED”}, {“id”:"2,“status”:“CHECKED”}]
请假 [{“id”:“1”,“status”:“CHECKED”}, {“id”:“2”,“status”:“UNCHECKED”}]
需要查询data中id=1且status=CHECKED的数据
sql: SELECT * FROM project
WHERE JSON_CONTAINS(data,JSON_OBJECT(‘id’, “1”, “status”, “CHECKED”));
模糊查询:
SELECT * FROM project
WHERE JSON_EXTRACT(check_data,’$[*].status’) LIKE ‘%CHECKED%’; -
值为json对象
name data
合同 {“id”:“1”,“status”:“UNCHECKED”}
请假 {“id”:“2”,“status”:“UNCHECKED”}
需要查询data中id=1
sql: SELECT * FROM project WHERE data -> ‘$.id’ = ‘1’;