mysql Json查询

1、test 数据表

id tos 
1  [{"option_id": "1", "target_id": "718"}, {"option_id": "3", "target_id": "-1"}, {"option_id": "5", "target_id": "-1"}]
2  [{"option_id": "0", "target_id": "-1"}]
3  NULL

// 针对tos 的指定字段查询
select  * from test where JSON_CONTAINS(tos,JSON_OBJECT('target_id', '-1'));
// 查询tos 数据内容
// 查询出一次后，不再查询
select  * from test where JSON_SEARCH(tos, 'one', "-1") is not null;
// 查询所有
select  * from test where JSON_SEARCH(tos, 'one', "5") is not null;

2、type 数据表

id type_ids
1 [2, 1, 3]
2 [6]
3 []

SELECT * FROM al_activities WHERE JSON_CONTAINS(type_ids, JSON_ARRAY(1));
SELECT * FROM al_activities WHERE JSON_CONTAINS(type_ids, JSON_ARRAY(1,2));