mysql的JSON字段查询

[jyone]

订单表中room字段中有一条记录是这样子：

[{"room_id": 22, "roomName": "306号房间"}, {"room_id": 24, "roomName": "308号房间"}]

请问如何查找包含roomName为306的记录呢？
我这样写不行select * from bill where room->'$

• .roomName' = "306" ;
  
  求正确的写法。

[jyone]

这个是示例哦，这样子写取不到数据 select * from bill where room->'$

• .roomName' = "306号房间" ;

[ACMAIN_CHM]

JSON_CONTAINS(json_doc, val[, path]) Returns 0 or 1 to indicate whether a specific value is contained in a target JSON document, or, if a path argument is given, at a specific path within the target document. Returns NULL if any argument is NULL or the path argument does not identify a section of the target document. An error occurs if either document argument is not a valid JSON

[zjcxc]

room->'$

• .roomName'  返回的是一个 JSON 数组，所以外面再套一层 JSON_CONTAINS 才行 where JSON_CONTAINS
• ( room->'$
• .roomName' , '"306号房间"', '$'
• )