MySQL JSON字段部分查询语法

模糊匹配jsonObject字段

 select * from tableName where columnName -> '$.xx' like '%xx%'

精确匹配jsonObject类型字段

 select * from tableName where columnName -> '$.xx' = 'xx'

模糊匹配jsonArray字段

 select * from tableName where columnName -> '$[*].xx' like '%xx%'

精确匹配jsonArray字段

select * from tableName where JSON_CONTAINS(columnName, JSON_OBJECT('xx','xx')) 

jsonArray 取值

select JSON_UNQUOTE(json_extract(columnName,'$[0]')) from tableName

jsonObject 取值

select JSON_EXTRACT(columnName,'$.xx') from tableName