Check if string exists in JSON array in SQL-CodePudding

Trying to return rows that the JSON value contains a certain value.

Table Structure
ID     Mapping
1      {"DepartmentID":"Finishing","ShipToIDs":[],"ItemIDs":[]}
2      {"DepartmentID":"Finishing","ShipToIDs":["100"],"ItemIDs":["300-VLR1022A"]}
3      {"DepartmentID":"Finishing","ShipToIDs":["100"],"ItemIDs":[]}

For example, passing in ShipToIDs = 100 would return rows 2-3 and passing in ItemID = 300-VLR1022A would return row 2.

CodePudding user response：

You will need SQL Server 2016 or later to make use of this, but it seems that you are looking for JSON_QUERY (Transact-SQL), e.g.:

/*
 * Data setup...
 */
create table dbo.Example (
  ID int,
  Mapping nvarchar(max)
);

insert dbo.Example (ID, Mapping) values
  (1, N'{"DepartmentID":"Finishing","ShipToIDs":[],"ItemIDs":[]}'),
  (2, N'{"DepartmentID":"Finishing","ShipToIDs":["100"],"ItemIDs":["300-VLR1022A"]}'),
  (3, N'{"DepartmentID":"Finishing","ShipToIDs":["100"],"ItemIDs":[]}');

/*
 * JSON_QUERY examples...
 */
select ID
from dbo.Example
cross apply openjson(Mapping, '$.ShipToIDs') ShipToID
where ShipToID.Value = '100';

select ID
from dbo.Example
cross apply openjson(Mapping, '$.ItemIDs') ItemID
where ItemID.Value = '300-VLR1022A';

Which yields the results:

ID
2
3

and:

ID
2

CodePudding user response：

From SQL Server 2017 , you can use OPENJSON with a dynamic path

DECLARE @path nvarchar(max) = N'$.ShipToIDs';
DECLARE @value nvarchar(max) = N'100';

SELECT *
FROM YourTable t
WHERE EXISTS (SELECT 1
    FROM OPENJSON(t.Mapping, @path) j
    WHERE j.value = @value);

db<>fiddle