mysql_json函数使用

文章目录

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 直接使用这个来查询还可以模糊匹配

	SELECT
	JSON_SEARCH(
'[{"id":10002444,"qrCode":"44444417","sticker":"44444416","deckId":"N3MWSF52666","vehicleType":"SCOOTER"},{"id":10002444,"qrCode":"44444416","sticker":"44444416","deckId":"N3MWSF52666","vehicleType":"SCOOTER"}]',
		'all',
		'%44444%',
	NULL,
	'$**.sticker')

SELECT * FROM incident_report WHERE (JSON_SEARCH(users,'one','39717',null,'$**.id') is not null)
SELECT users FROM incident_report LIMIT 10  15890    qrCode  44444403
SELECT JSON_SEARCH(users,'one','%51230%',null,'$**.qrCode') FROM incident_report LIMIT 10

2.JSON_EXTRACT(解析json数据),使用JSON_CONTAIN来查询 解析后的JSON信息

对对于是数组里面的特定对象字段譬如 下面这种,这需要进一步抽取

[{"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)