I have the following Table 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"}]}
I’m trying to query where id = 2 and partNum = 2423
Here is what I wrote so far:
select * from mytable where id = 2 and '2423' IN (select value from OPENJSON(JSON_QUERY(Json, '$.partList'), '$.part'))
What would be the most efficient way to query?
Advertisement
Answer
Here is what worked for me.
select * from mytable CROSS apply OPENJSON(Json, '$.partList') WITH ( partnumber nvarchar(100) '$.partNum' ) where partnumber = '2423' and id = 2