查询json列中的键值

Question

我有一个表格"job "其中一列名为"check_list"。(varchar(max)有JSON值，一个示例值将是

{
"items":[
{
"name":"machine 1",
"state":"",
"comment":"",
"isReleaseToProductionCheck":true,
"mnachine_id":10
},
{
"name":"machine 2",
"state":"",
"comment":"",
"isReleaseToProductionCheck":true,
"machine_id":12
}
]
}

现在我如何编写一个SQL查询来只返回列"check_list"条目[machine_id] = 12

Answer 1

在经过一些尝试和错误之后，这是适合我的解决方案。我必须添加ISJSON检查因为一些旧数据无效

WITH jobs (id, workorder, selectedMachine) AS(
SELECT 
[id], 
[workorder], 
(
select 
* 
from 
openjson(check_list, '$.items') with (machine_id int '$.machine_id') 
where 
machine_id = 12
) as selectedMachine 
FROM 
engineering_job_schedule 
WHERE 
ISJSON(check_list) > 0
) 
Select 
* 
from 
jobs 
where 
selectedMachine = 12