How to Filter Json Object in Json Array?

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,fromCompanyIdfrom openjson(@json,'$.items') j --path to the main arraycross apply openjson(value,'$.ids') -- path inside element of main arraywith(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,fromCompanyIdfrom @tblcross apply openjson(detail,'$.items') -- path to the main arraycross apply openjson(value,'$.ids') -- path inside array elementwith(jid int '$')cross apply openjson(value)with (fromCompanyId int '$.fromCompanyId')where fromCompanyId=4

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.