How can I find a record that equals a value inside a JSON array

Question

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?

Answer 1

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