时间戳

首页 > 解决方案 > 在 SQL Server 表中，如何根据对具有 JSON 值的列的 JSON 搜索过滤记录

json - 在 SQL Server 表中，如何根据对具有 JSON 值的列的 JSON 搜索过滤记录

问题描述

我在过滤 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

通过在 Internet 上进行研究，我发现 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 功能或任何动态方式来实现相同功能？

标签： jsonsql-servertsql

解决方案

在使用 OPENJSON 时，我唯一能想到的选择是将搜索字符串分解为其键值对，将存储要搜索的 json 的表分解为其键值对并加入。

有一些限制需要注意：

  1. 此解决方案不适用于 json 中的嵌套数组
  2. 搜索将是 OR 不是 AND。这意味着如果我传入多个我正在搜索的“部门”，例如'{“Department”：[“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 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"]}

推荐阅读