在 SQL Server 表中，如何根据对具有 JSON 值的列的 JSON 搜索筛选记录

Question

我在筛选SQL Server 2017表中的记录时面临挑战，该表具有具有JSON类型值的VARCHAR列：

包含 JSON 列值的示例表行：

Row # 1. {"Department":["QA"]}   
Row # 2. {"Department":["DEV","QA"]}    
Row # 3. {"Group":["Group 2","Group 12"],"Cluster":[Cluster 11"],"Vertical": 
["XYZ"],"Department":["QAT"]}          
Row # 4. {"Group":["Group 20"],"Cluster":[Cluster 11"],"Vertical":["XYZ"],"Department":["QAT"]}

现在，我需要根据输入参数从此表中筛选记录，该参数可以采用以下格式：

要查询的示例 JSON 输入参数：

1. `'{"Department":["QA"]}'` -> This should return Row # 1 as well as Row # 2.   
2. `'{"Group":["Group 2"]}'` -> This should return only Row # 3.

因此，搜索应该像列值包含"具有任何匹配值的任何可用 json 标记"一样，然后返回这些匹配的记录。

注意 - 这与PostgreSQL

---

jsonb完全相同，如下所示：PostgreSQL过滤器子句：

TableName.JSONColumnName @> '{"Department":["QA"]}'::jsonb

---

通过研究互联网，我发现了SQL Server中可用的OPENJSON功能，其工作原理如下。

OPENJSON 示例示例：

SELECT * FROM
tbl_Name UA
CROSS APPLY OPENJSON(UA.JSONColumnTags)
WITH ([Department] NVARCHAR(500) '$.Department', [Market] NVARCHAR(300) '$.Market', [Group] NVARCHAR(300) '$.Group'       
) AS OT
WHERE
OT.Department in ('X','Y','Z')
and OT.Market in ('A','B','C')

但这种方法的问题在于，如果将来需要在 JSON 中支持任何新标记(如"Area")，则还需要将其添加到实现此逻辑的每个存储过程中。

我是否缺少任何现有的SQL Server 2017功能或任何动态实现这些功能的方法？

Answer 1

使用 OPENJSON 时，我唯一能想到的选项就是将您的搜索字符串分解为其键值对，将存储您要搜索的 json 的表分解到其键值对中并连接。

需要注意以下限制：

1. 此解决方案不适用于 json 中的嵌套数组
2. 搜索将是或不是和。 这意味着如果我传入我正在搜索的多个"部门"，例如"{"部门"：["QA"，"DEV"]}"，它将返回具有任一值的行，而不是仅包含两个值的行。

下面是一个工作示例：

DECLARE @TestData TABLE
(
[TestData] NVARCHAR(MAX)
);
--Load Test Data
INSERT INTO @TestData (
[TestData]
)
VALUES ( '{"Department":["QA"]}' )
, ( '{"Department":["DEV","QA"]}' )
, ( '{"Group":["Group 2","Group 12"],"Cluster":["Cluster 11"],"Vertical": ["XYZ"],"Department":["QAT"]}' )
, ( '{"Group":["Group 20"],"Cluster":["Cluster 11"],"Vertical":["XYZ"],"Department":["QAT"]}' );
--Here is the value we are searching for
DECLARE @SeachJson NVARCHAR(MAX) = '{"Department":["QA"]}';
DECLARE @SearchJson TABLE
(
[Key] NVARCHAR(MAX)
, [Value] NVARCHAR(MAX)
);
--Load the search value into a temp table as its keyvalue pair.
INSERT INTO @SearchJson (
[Key]
, [Value]
)
SELECT      [a].[Key]
, [b].[Value]
FROM        OPENJSON(@SeachJson) [a]
CROSS APPLY OPENJSON([a].[Value]) [b];
--Break down TestData into its keyvalue pair and then join back to the search table.
SELECT     [TestData].[TestData]
FROM       (
SELECT      [a].[TestData]
, [b].[Key]
, [c].[Value]
FROM        @TestData [a]
CROSS APPLY OPENJSON([a].[TestData]) [b]
CROSS APPLY OPENJSON([b].[Value]) [c]
) AS [TestData]
INNER JOIN @SearchJson [srch]
ON [srch].[Key] COLLATE DATABASE_DEFAULT = [TestData].[Key]
AND [srch].[Value] = [TestData].[Value];

这将为您提供以下结果：

TestData
-----------------------------
{"Department":["QA"]}
{"Department":["DEV","QA"]}