时间戳

首页 > 解决方案 > T-SQL - 在过滤的 JSON 数组中搜索

json - T-SQL - 在过滤的 JSON 数组中搜索

问题描述

2017 年的 SQL 服务器。

表有存储 JSON 的OrderData列。DataProperties存储在那里的 JSON 示例：

{
 "Input": {
   "OrderId": "abc",
   "Data": [
     {
       "Key": "Files",
       "Value": [
         "test.txt",
         "whatever.jpg"
       ]
     },
     {
       "Key": "Other",
       "Value": [
         "a"
       ]
     }
   ]
 }
}

因此，它是一个带有 Input 对象的对象，它具有 KVP 的 Data 数组 - 充满了带有 Key 字符串和 Value 字符串数组的对象。

还有我的问题 - 我需要根据示例 JSON 中的文件中的值查询行 - 简单的 LIKE 匹配%text%。

此查询有效：

SELECT TOP 10 *
FROM OrderData CROSS APPLY OPENJSON(DataProperties,'$.Input.Data') dat
WHERE JSON_VALUE(dat.value, '$.Key') = 'Files' and dat.[key] = 0 
AND JSON_QUERY(dat.value, '$.Value') LIKE '%2%'

问题是这个查询很慢，这不足为奇。

如何让它更快？

  1. 我无法使用 JSON_VALUE 创建计算列，因为我需要在数组中进行过滤。
  2. 我无法在“$.Input.Data”或“$.Input.Data[0].Values”上使用 JSON_QUERY 创建计算列 - 因为我需要在这个数组中使用Key == "Files".

我已经搜索过，但您似乎无法创建也过滤数据的计算列，就像这次尝试一样：

ALTER TABLE OrderData
 ADD aaaTest AS (select JSON_QUERY(dat.value, '$.Value')
 OPENJSON(DataProperties,'$.Input.Data') dat
WHERE JSON_VALUE(dat.value, '$.Key') = 'Files' and dat.[key] = 0 );

错误：Subqueries are not allowed in this context. Only scalar expressions are allowed.

我有哪些选择？

  1. 添加带有索引的 Files 列并使用在插入/更新时填充此列的 INSERT/UPDATE 触发器？
  2. 创建一个“计算”该列的视图？无法添加索引，还是会很慢

到目前为止，只有选项 1. 有一些优点，但我不喜欢触发器，也许还有另一种选择？

标签： jsontsqlsql-server-2017

解决方案

你可以试试这个：

注意：我添加了一个2来text2填充您的过滤器。我将两者都命名为复数"Values"：

DECLARE @mockupTable TABLE(ID INT IDENTITY, DataProperties NVARCHAR(MAX));
INSERT INTO @mockupTable VALUES
(N'{
 "Input": {
   "OrderId": "abc",
   "Data": [
     {
       "Key": "Files",
       "Values": [
         "test2.txt",
         "whatever.jpg"
       ]
     },
     {
       "Key": "Other",
       "Values": [
         "a"
       ]
     }
   ]
 }
}');

查询

SELECT TOP 10 *
FROM @mockupTable t 
CROSS APPLY OPENJSON(t.DataProperties,'$.Input.Data')
WITH([Key] NVARCHAR(100)
    ,[Values] NVARCHAR(MAX) AS JSON) dat
WHERE dat.[Key] = 'Files'
AND dat.[Values] LIKE '%2%';

主要区别在于 -WITH子句，它用于以类型化的方式并并排地返回对象内部的属性（类似于所有列的裸体- 但要好得多）。这避免了在您的...中使用昂贵的 JSON 方法OPENJSONPIVOTWHERE

提示：当我们返回Valuewith 时，NVARCHAR(MAX) AS JSON我们可以继续嵌套数组，并可能继续进行如下操作：

SELECT TOP 10 *
FROM @mockupTable t 
CROSS APPLY OPENJSON(t.DataProperties,'$.Input.Data')
WITH([Key] NVARCHAR(100)
    ,[Values] NVARCHAR(MAX) AS JSON) dat
WHERE dat.[Key] = 'Files'
--we read the array again with `OPENJSON`:
AND 'test2.txt' IN(SELECT [Value] FROM OPENJSON(dat.[Values]));

您可以再使用一个CROSS APPLY来添加数组的值并WHERE直接对其进行过滤。

SELECT TOP 10 *
FROM @mockupTable t 
CROSS APPLY OPENJSON(t.DataProperties,'$.Input.Data')
WITH([Key] NVARCHAR(100)
    ,[Values] NVARCHAR(MAX) AS JSON) dat
CROSS APPLY OPENJSON(dat.[Values]) vals
WHERE dat.[Key] = 'Files'
  AND vals.[Value]='test2.txt'

只是检查一下...

推荐阅读