TSQL How do i check array is a subset using JSON_QUERY

Question

I have the following Table Documents where data is stored in JSON format.

DocumentID     Status        Data
------------------------------------------
1              Active        '{ "AccountNumber":["A1","A2","A3","A4"] }'
2              Active        '{ "AccountNumber":["A1","A3"] }'
3              Active        '{ "AccountNumber":["A2","A4"] }'
4              Active        '{ "AccountNumber":["A1"] }'

Then i have filter which is also a json

 DECLARE @filter = '{ "AccountNumber":["A2","A3"] }'

How do i apply filter in where clause.

The expected result should return matching Documents, DocumentID 1,2,3

SELECT DocumentID 
FROM Documents D
WHERE
JSON_QUERY(D.Data,'$.AccountNumber') IN JSON_QUERY($(@filter,'$.AccountNumber') -- This is not working

score 3 · Accepted Answer · answered May 22 '20 at 19:52

3

select DocumentID
from
  Documents D
where
  exists (select value from openjson(D.data, '$.AccountNumber')
          intersect
          select value from openjson(@filter, '$.AccountNumber')
         )
;

select distinct DocumentID
from
  Documents D
  cross apply openjson(D.data, '$.AccountNumber') j
  inner join openjson(@filter, '$.AccountNumber') f on j.value = f.value
;

answered May 22 '20 at 19:52

GSerg

76,472
17
159
346

Is Json comparison not TypeSafe? meaning if i have stored AccountNumber as integer array [111,222,333] in DB, but if filter is string array ['111','222'] it still finds the matching records. ( vice versa also works) – LP13 May 22 '20 at 20:46
@LP13 It's not about json. As it unwraps json into columns, normal SQL Server rules apply, and according to the [data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql), varchars are converted to ints first when they are being compared to ints. – GSerg May 22 '20 at 20:52
@LP13 `OPENJSON()` with default schema (without the `WITH` clause) [returns](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15#return-value) a table with columns `key`, `value` and `type` and the `value` column is a `nvarchar(max)` column. So, both calls (`SELECT [value] FROM OPENJSON('["111","222"]')` and `SELECT [value] FROM OPENJSON('[111,222]'`) return `AccountNumber` as an `nvarchar` column. – Zhorov May 23 '20 at 08:48

TSQL How do i check array is a subset using JSON_QUERY

1 Answers1