For example, now there is a table with id, grade two fields, grade is JSON type

If I want to consult the corresponding JSON object according to the id value how to find the

Grade column JSON format sample below

/
{
"Id" : "f69 DE b814d6ac - 68-4-9 e41-5 b9de58ed65f", 
"Name" : "one day", 
"Time" : 1, 
"Type" : "day", 
"Price" : 55 
}, 
{
"Id" : "f81 bbfc6bfe - 2-40 a5 - e6e0b3bdeee b027-1", 
"Name" : "three days", 
"Time" : 3, 
"Type" : "day", 
"Price" : 70 
}, 
{
"Id" : "2 a7fdfd6-8056-417 - b - 8 f26 - f0f595265373", 
"Name" : "a week," 
"Time" : 7, 
"Type" : "day", 
"Price" : 300 
}, 
{
"Id" : "3 b9a2ba9-5 dad - 40-8 f50e57396d e93-83", 
"Name" : "half an hour," 
"Time" : 0.5, 
"Type" : "hour", 
"Price" : 7 
}, 
{
"Id" : "4 facc993-3409-41 eb - b16707e0 b078-8959", 
"Name" : "an hour," 
"Time" : 1, 
"Type" : "hour", 
"Price" : 14 
}, 
{
"Id", "77642501-5 - b277 f1d - 4757-9 df7571637cb", 
"Name" : "for three hours," 
"Time" : 3, 
"Type" : "hour", 
"Price" : 30 
} 
] 

Now I can only check the array of all content

SQL:

 SELECT grade - & gt; '$[*] FROM levels WHERE grade - & gt; '$[*]. Id' like '% 77642501-5 - b277 f1d - 4757-9 df7571637cb %'; 

Results:

[{"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}]

CodePudding user response:

MySQL json path do not seem to support conditions

CodePudding user response:

you have a problem that the design of the table, put a table this list should be separate

CodePudding user response:

https://bbs.csdn.net/topics/392275882
Here are the answers