How to use mysql “json_contains” with JPA Specification - Stack Overflow
How to search JSON array in MySQL? - Stack Overflow
json - MySQL JSON_EXTRACT value of property based on criteria - Stack Overflow
spring jpa function(函数) JSON_EXTRACT 复杂查询,jpa josn查询_风.foxwho的博客-CSDN博客_jpa json查询
MySql 中文文档 - 12.17.3 搜索 JSON 值的函数 | Docs4dev
json字符串搜索模糊匹配等相关的内容
1.JSON_SEARCH 直接使用这个来查询还可以模糊匹配
1 | SELECT |
1 | SELECT * FROM incident_report WHERE (JSON_SEARCH(users,'one','39717',null,'$**.id') is not null) |
2.JSON_EXTRACT(解析json数据),使用JSON_CONTAIN来查询 解析后的JSON信息
对对于是数组里面的特定对象字段譬如 下面这种,这需要进一步抽取
1 | [{"id":10002444,"qrCode":"44444417","sticker":"44444416","deckId":"N3MWSF52666","vehicleType":"SCOOTER"},{"id":10002444,"qrCode":"44444416","sticker":"44444416","deckId":"N3MWSF52666","vehicleType":"SCOOTER"}] |
JSON_CONTAINS(JSON_EXTRACT(json_field, ‘$[*].id’), 11, ‘$’)
SELECT JSON_EXTRACT(users,'$.id') FROM incident_report LIMIT 10
SELECT JSON_EXTRACT(users, '$[*].userTrips[*].id') FROM incident_report LIMIT 10
SELECT users,JSON_CONTAINS(JSON_EXTRACT(users, '$[*].userTrips[*].id'), '12689', '$') FROM incident_report LIMIT 10
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html)