[{"room_id": 22, "roomName": "306号房间"}, {"room_id": 24, "roomName": "308号房间"}]
请问如何查找包含roomName为306的记录呢？
我这样写不行select * from bill where room->'$[*].roomName' = "306" ;
求正确的写法。
3 个解决方案
#1
这个是示例哦，这样子写取不到数据
select * from bill where room->'$[*].roomName' = "306号房间" ;
select * from bill where room->'$[*].roomName' = "306号房间" ;
#2
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
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
#3
room->'$[*].roomName' 返回的是一个 JSON 数组，所以外面再套一层 JSON_CONTAINS 才行
where JSON_CONTAINS( room->'$[*].roomName' , '"306号房间"', '$')
where JSON_CONTAINS( room->'$[*].roomName' , '"306号房间"', '$')
#1
这个是示例哦，这样子写取不到数据
select * from bill where room->'$[*].roomName' = "306号房间" ;
select * from bill where room->'$[*].roomName' = "306号房间" ;
#2
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
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
#3
room->'$[*].roomName' 返回的是一个 JSON 数组，所以外面再套一层 JSON_CONTAINS 才行
where JSON_CONTAINS( room->'$[*].roomName' , '"306号房间"', '$')
where JSON_CONTAINS( room->'$[*].roomName' , '"306号房间"', '$')