CODEWITHSUNDEEP
sqlarraysjsonsql-servert-sql
Brds
Brds

Reputation: 1076

Checking to see if one or more ids is in a json string

If I have the following rows in a table:

clientId    settings
1           {"franchises":[1,7,9,11,14,20,23,26,27,29,33,34,35,45,49,50,57,58,72,73]}
2           {"franchises":[1]}
3           {"franchises":[50]}

How would I query that to pull clientIds who have the franchises 1 or 50?

I tried doing 

SELECT clientId FROM clientSettings WHERE JSON_VALUE(settings, '$.franchises') IN (1,50)

but that didn't work.

Upvotes: 3

Views: 136

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, try with below query using "CROSS APPLY OPENJSON" (SQL-FIDDLE):

SELECT c.clientId
FROM clientSettings c
CROSS APPLY OPENJSON(c.settings, '$.franchises') with (franchise integer '$') as f
WHERE f.franchise in (1, 50)
GROUP BY c.clientId;

Upvotes: 0

Alan Burstein
Alan Burstein

Reputation: 7928

Another way that requires no sorting or aggregating. 

DECLARE @clientSettings TABLE (ClientId INT IDENTITY, Settings VARCHAR(8000));

INSERT @clientSettings(settings)
VALUES
('{"franchises":[0,7,9,11,14,20,23,26,27,29,33,34,35,45,49,55,57,58,72,73,1,50]}'),
('{"franchises":[1]}'),
('{"franchises":[50]}');

SELECT c.clientId
FROM   @clientSettings c
CROSS APPLY
(
    SELECT TOP (1) f.franchise
    FROM   OPENJSON(c.settings, '$.franchises') WITH (franchise INT '$') AS f
    WHERE  f.franchise IN (1, 50)
) AS x;

Upvotes: 0

GMB
GMB

Reputation: 222702

You can use json functions:

select distinct c.clientId
from clientSettings c
cross apply openjson(c.settings, '$.franchises') with (franchise integer '$')
where franchise in (1, 50);

Wrapping this in exists might perform better, since it avoids the need for aggregation:

select c.clientId
from clientSettings c
where exists (
    select 1
    from openjson(c.settings, '$.franchises') with (franchise integer '$')
    where franchise in (1, 50)
)

Demo on DB Fiddle (I added a non-matching line to your dataset, with id 4):

| clientId |
| -------: |
|        1 |
|        2 |
|        3 |

Upvotes: 3

Related Questions