如，現在有張表 有 id、grade 兩個欄位，grade是JSON型別
如果我要根據 id 值查找對應的JSON物件 如何查找
grade列JSON格式示例如下
[
{
"id": "b814d6ac-68de-4f69-9e41-5b9de58ed65f",
"name": "一天",
"time": 1,
"type": "day",
"price": 55
},
{
"id": "bbfc6bfe-2f81-40a5-b027-1e6e0b3bdeee",
"name": "三天",
"time": 3,
"type": "day",
"price": 70
},
{
"id": "2a7fdfd6-8056-417b-8f26-f0f595265373",
"name": "一周",
"time": 7,
"type": "day",
"price": 300
},
{
"id": "3b9a2ba9-5dad-40be-8e93-83f50e57396d",
"name": "半小時",
"time": 0.5,
"type": "hour",
"price": 7
},
{
"id": "4facc993-3409-41eb-b078-8959b16707e0",
"name": "一小時",
"time": 1,
"type": "hour",
"price": 14
},
{
"id": "77642501-5f1d-4757-b277-9df7571637cb",
"name": "三小時",
"time": 3,
"type": "hour",
"price": 30
}
]
我現在只能查到 陣列的所有內容
SQL:
SELECT grade->'$[*]' FROM levels WHERE grade->'$[*].id' like '%77642501-5f1d-4757-b277-9df7571637cb%';
結果：
[{"id": "b814d6ac-68de-4f69-9e41-5b9de58ed65f", "name": "一天", "time": 1, "type": "day", "price": 55}, {"id": "bbfc6bfe-2f81-40a5-b027-1e6e0b3bdeee", "name": "三天", "time": 3, "type": "day", "price": 70}, {"id": "2a7fdfd6-8056-417b-8f26-f0f595265373", "name": "一周", "time": 7, "type": "day", "price": 300}, {"id": "3b9a2ba9-5dad-40be-8e93-83f50e57396d", "name": "半小時", "time": 0.5, "type": "hour", "price": 7}, {"id": "4facc993-3409-41eb-b078-8959b16707e0", "name": "一小時", "time": 1, "type": "hour", "price": 14}, {"id": "77642501-5f1d-4757-b277-9df7571637cb", "name": "三小時", "time": 3, "type": "hour", "price": 30}]
uj5u.com熱心網友回復：
MySQL 的 json path 中似乎不支持條件uj5u.com熱心網友回復：
你 這表的設計有問題，這種串列應該單獨放一個表里
uj5u.com熱心網友回復：
https://bbs.csdn.net/topics/392275882這里有答案
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79529.html
標籤：MySQL
下一篇：mysql 1064.