1

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ray
  • 325
  • 1
  • 5
  • 15
  • "What would be the most efficient way ..." -- Not to (a)buse JSON in the first place but relational means like a linking table... – sticky bit Jul 30 '21 at 22:23
  • I agree, but i did not have any other options or a say in db design. Thanks for the response. – Ray Aug 12 '21 at 17:56

1 Answers1

0

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
Dharman
  • 30,962
  • 25
  • 85
  • 135
Ray
  • 325
  • 1
  • 5
  • 15