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? https://stackoverflow.com/questions/54014774/how-to-do-where-clause-on-simple-json-array-in-sql-server-2017 The answer using OPENJSON seems relevant here. — Jacob H, May 02 '19 at 19:14
https://stackoverflow.com/questions/38285223/accessing-json-array-in-sql-server-2016-using-json-value also — JamieD77, May 02 '19 at 19:26
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, May 02 '19 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, May 02 '19 at 19:31
`openjson(Details, '$.items') WITH( [fromCompanyId] int '$.fromCompanyId')))` — Alex Kudryashev, May 02 '19 at 19:36
@AlexKudryashev - Thanks. This got me to the answer the needed. — chobo2, May 02 '19 at 19:51

Alex Kudryashev · Accepted Answer · 2019-05-02T21:24:26.497

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

score 0 · Answer 2 · answered May 02 '19 at 21:32

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 02 '19 at 21:32

Anup Shah

1,256
10
15

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 May 03 '19 at 23:19
1

Generally, this is not an answer, rather a comment. I didn't downvote it (yet). – Alex Kudryashev May 03 '19 at 23:24
yes, `sometimes one need to access third party (stupid) data`. and i am just pointing out not to do that in SQL. – Anup Shah May 07 '19 at 14:54

How to Filter Json Object in Json Array?

2 Answers2