我在过滤 SQL Server 2017 表中的记录时面临挑战，该表有一个 VARCHAR 列，该列具有 JSON 类型值：
具有 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 中的任何新标签（如“区域”），则还需要将其添加到实现此逻辑的每个存储过程中。
是否有我缺少的任何现有 SQL Server 2017 功能或实现相同功能的任何动态方法？
在使用 OPENJSON 时，我唯一能想到的一个选项是将您的搜索字符串分解为其键值对，将存储要搜索的 json 的表分解为其键值对并加入。
需要注意以下限制：
这是一个工作示例：
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 key\value 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 key\value 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"]}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句