我如何找到一个记录等于JSON数组中的值

Question

我有如下表mytable

id   |      Json        
1    | {"test":[], "partList":[{"partid": 44, "partNum": "1234"}, {"partid": 34, "partNum": "2423"}]}
2    | {"test":[], "partList":[{"partid": 23, "partNum": "8343"}, {"partid": 34, "partNum": "2423"}]}

我试图查询where id = 2 and partNum = 2423

到目前为止我写的是:

select *
from mytable
where id = 2
and '2423' IN (select value from OPENJSON(JSON_QUERY(Json, '$.partList'), '$.part'))

最有效的查询方式是什么?

Answer 1

我是这么做的。

select *
from mytable
CROSS apply OPENJSON(Json, '$.partList') WITH ( partnumber nvarchar(100) '$.partNum' )
where partnumber = '2423'
and id = 2