JSON对象数组的SQL Server索引

vwkv1x7d 于 2023-04-22 发布在 SQL Server

关注(0)|答案(1)|浏览(151)

我得到了一个表db_elements，其中有两列。Id（binary(255)类型的主键）和event（nvarchar(max)类型的JSON内容）。event列的示例内容是以下格式的JSON：

{
    "id": 123,
    "name": "test",
    "elements": [{
        "element_id": 45,
        "element_type": "type_01"
    }, {
        "element_id": 65,
        "element_type": "type_01"
    }, {
        "element_id": 87,
        "element_type": "type_02"
    }]
}

JSON数组“elements”中元素的数量因行而异，但其中对象的格式始终相同，尽管我在这里简化了JSON。
现在，我想使用如下查询来查询表：

SELECT id, event FROM db_elements 
CROSS APPLY OPENJSON(event, '$.elements') WITH (
    element_id int '$.element_id', 
    element_type nvarchar(max) '$.element_type'
) WHERE element_id = 87 AND element_type = 'type_02';

查询本身工作正常，但我想创建一个索引来加速执行，因为在我的用例中，表中的条目数量已经相当大，并且会随着时间的推移而增加。我只能在文档和堆栈溢出中找到简单字段的索引示例，如'name'字段，并且不知道如何解决这个问题。
所以我的问题是：

有没有一种方法可以为JSON数组创建一个索引来加速查询的执行？
如果是，如何进行？
如果否，是否有解决此问题的方法？

JSON

来源：https://stackoverflow.com/questions/76071301/sql-server-index-for-json-array-of-objects

1条答案

按热度按时间

8mmmxcuj1#

您可以使用架构绑定标量函数创建计算列，然后对其进行索引。

CREATE FUNCTION dbo.IsMyJson(@json nvarchar(max))
RETURNS bit
WITH SCHEMABINDING
AS BEGIN
  RETURN CASE WHEN EXISTS (SELECT 1
    FROM OPENJSON(@json, '$.elements')
      WITH (
        element_id int '$.element_id', 
        element_type nvarchar(max) '$.element_type'
      )
      WHERE element_id = 87
        AND element_type = 'type_02'
  ) THEN 1 ELSE 0 END;
END;

CREATE TABLE db_elements (
  id int IDENTITY PRIMARY KEY,
  event nvarchar(max),
  is_my_Json AS dbo.IsMyJson(event),
  INDEX IX NONCLUSTERED (is_my_Json, id) INCLUDE (event)
)

然后只需查询计算列，索引就会被使用。

SELECT *
FROM db_elements
WHERE is_my_Json = 1;

您也可以更改函数以返回实际数据，尽管在这种情况下，您不能索引nvarchar(max)，因为它太大了（您需要将其转换为更短的）。
db<>fiddle

赞(0）回复(0）举报 2023-04-22

我来回答

JSON对象数组的SQL Server索引

1条答案

相关问题

热门标签

最新问答