How to Filter Json Object in Json Array?

Question

I have some data stored in a sql column that looks like

{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}

now I want to make a where clause that would find all "items" that have a "fromCompanyId" of "4".

All I found so far is

WHERE JSON_VALUE(jsonInfo,'$.info.address[0].state') LIKE 'US%'

but they are hardcoding the array index. I need to find all matches.

I also am trying openJson but still not working

where 4 in (select fromCompanyId  from openjson(Details, '$.items.fromCompanyId') WITH( [fromCompanyId] int '$.fromCompanyId')))

Didn't you ask this same question back in January? stackoverflow.com/questions/54014774/… The answer using OPENJSON seems relevant here. — Jacob H
– Jacob H, Commented May 2, 2019 at 19:14
It is similar but that was with an array of strings. This is array of json objects and when I tried openJson it did not work, so I maybe did it wrong. — chobo2
– chobo2, Commented May 2, 2019 at 19:26
@JamieD77 - I took a look at that link but still not working for me so I might be doing it wrong. I updated my OP. — chobo2
– chobo2, Commented May 2, 2019 at 19:31
openjson(Details, '$.items') WITH( [fromCompanyId] int '$.fromCompanyId'))) — Alex Kudryashev
– Alex Kudryashev, Commented May 2, 2019 at 19:36

Alex Kudryashev · Accepted Answer · 2019-05-02 21:24:26Z

You need to openjson on several levels. Something like this.

declare @json nvarchar(max)=N'{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}'

select id,fromCompanyId
from openjson(@json,'$.items') j --path to the main array
cross apply openjson(value,'$.ids') -- path inside element of main array
with(id int '$')
cross apply openjson(value)
with (
fromCompanyId int '$.fromCompanyId'
)
where fromCompanyId=4

Similar with a table field.

declare @tbl table (id int, detail nvarchar(max))
insert @tbl (id,detail) values
(1,N'{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}'),
(2,N'{
  "items": [
    { "ids": [5], "fromCompanyId": 4 },
    { "ids": [7,9], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}')

select id,jid,fromCompanyId
from @tbl
cross apply openjson(detail,'$.items') -- path to the main array
cross apply openjson(value,'$.ids') -- path inside array element
with(jid int '$')
cross apply openjson(value)
with (
fromCompanyId int '$.fromCompanyId'
)
where fromCompanyId=4

Anup Shah · Accepted Answer · 2019-05-02 21:32:57Z

0

SQL is not a right place to this search. besides how bad the performance of this search would be, the query will be very expensive for DB resource like CPU and IO. the query will become exponentially slow as the data in table grows. even w/o much data growth if the actual JSON is more than 8000char (4k if stored as NVARCHAR) then it will be stored off the row hence every time it has to read BLOB.

rather i would recommend to just read from db and parse inn application side whatever language you have. that would be more cheaper.

in short: this is not a SQL task. you should look at the workflow and process improvement first. if the search query is regular user workflow, then schema design it self may not be right for this workflow.

answered May 2, 2019 at 21:32

Anup Shah

1,25410 silver badges15 bronze badges

3 Comments

Alex Kudryashev Over a year ago

This may make sense but has nothing to OP. MS SQL Server still doesn't have native support for json data (as it does for xml) but sometimes one need to access third party (stupid) data.

Alex Kudryashev Over a year ago

Generally, this is not an answer, rather a comment. I didn't downvote it (yet).

Anup Shah Over a year ago

yes, sometimes one need to access third party (stupid) data. and i am just pointing out not to do that in SQL.

Collectives™ on Stack Overflow

How to Filter Json Object in Json Array?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related