526互联

MySql Json字段部分查询语法

发布时间 2023-10-21 10:19:43作者: Kllin

模糊匹配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