文档:json查询文档
mysql5.7以上支持json的操作,以及增加了json存储类型
查看数据库版本
select version();
使用时注意:
- JSON列存储的必须是JSON格式数据,否则会报错。
- JSON数据类型是没有默认值的。
字典
JSON_CONTAINS(target, candidate[, path])
JSON_ARRAY([val[, val] …])
mysql
方式一:
SELECT *
FROM job
WHERE json_contains(json_extract(job.detail_info, '$.group_id_list'), json_array('bfeb29d6123511ed9a4902de6c74f2a2'))
ORDER BY job.create_time DESC
方式二:
SELECT *
FROM job
WHERE json_contains( job.detail_info,'"bfeb29d6123511ed9a4902de6c74f2a2"', '$.group_id_list')
ORDER BY job.create_time DESC ;
sqlachemy
db.session.query(ActionProxy).filter(
func.json_extract(ActionProxy.primary_configuration, "$.URL") == address).filter(
ActionProxy.plugin_id == plugin_id)
SELECT *
FROM job
WHERE json_contains( job.detail_info,‘bfeb29d6123511ed9a4902de6c74f2a2’, ‘$.group_id_list’)
ORDER BY job.create_time DESC
Invalid JSON text in argument 2 to function json_contains: “Invalid value.” at position 0.
字典套数组
JSON_EXTRACT(json_doc, path[, path] …)
mysql
方式一:
SELECT * FROM action_proxy
WHERE json_extract(action_proxy.primary_configuration,"$.URL")= "https://172.118.59.80/"
方式二:
SELECT * FROM action_proxy
WHERE action_proxy.primary_configuration -> "$.URL"= "https://172.118.59.80/";
sqlachemy
是精确匹配group_id这个对象
db.session.query(Job).filter(
func.json_contains(func.json_extract(Job.detail_info, "$.group_id_list"), func.json_array(group_id)))
补充:
字典套数组再套字典
mysql
SELECT *
FROM operation_log
WHERE json_contains(json_extract(operation_log.detail, '$.resources[*].name'), json_array('//'))