mysql 查询json字段 json_extract (mysql 5.7及以上)

  • 找第一层:
SELECT * FROM tourists WHERE json_data->'$.weixinOpenId' = '299485886686868'
或者
SELECT * FROM devices WHERE JSON_EXTRACT(json_data, "$.lcswAccessToken") = '33e5028835bf4d478ed5cd8f8a618ea5'

   

  • 找多层:
SELECT * FROM devices WHERE json_extract(json_extract(json_extract(json_data,"$.lastOperation"),"$.target"),"$.name") = '西门门岗闸机01'

   

 select * from tickets where JSON_CONTAINS(json_data,'"237241772918912"','$.ticketClassificationIds');
posted @ 2018-10-16 09:37  南侠书生  阅读(9452)  评论(0编辑  收藏  举报