检查一个或多个 id 是否位于 json 字符串中

Question

如果我在表中有以下行：

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]}

我将如何查询它以提取拥有特许经营权 1 或 50 的 clientId？

我试过做

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

但这没有用。

Answer 1

您可以使用 json 函数：

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

将其包装在exists中可能会表现得更好，因为它避免了聚合的需要：

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

DB Fiddle 上的演示(我在您的数据集中添加了一个不匹配的行，ID 为4(：

|

客户端标识 | |-------: | |       1 | |       2 | |       3 |

Answer 2

另一种不需要排序或聚合的方法。

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;

Answer 3

请尝试使用"交叉应用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;