结合使用SQL Server索引视图和OPENJSON

Question

我有一个只有一行和一个列的表，其中存储了一个包含大约30 it/16k对象的JSON数组：

CREATE TABLE [dbo].[CitiesTable] 
(
    [CitiesJson] [NVARCHAR](MAX) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[CitiesTable] ([CitiesJson]) 
VALUES ('{"cities":[{"cityName": "London","residentCount": 8961989},{"cityName": "Paris","residentCount": 2165423},{"cityName": "Berlin","residentCount": 3664088}]}')

我使用这个查询来解析JSON并将它带入关系结构中：

SELECT x.[CityName], x.[ResidentCount]
FROM
    OPENJSON((SELECT [CitiesJson] FROM dbo.CitiesTable), '$.cities') 
    WITH 
    (
        [CityName] [NVARCHAR] (50) '$.cityName',
        [ResidentCount] [INT] '$.residentCount'
    ) AS x

产生的结果：

CityName   ResidentCount
---------- -------------
London     8961989
Paris      2165423
Berlin     3664088

我想为此创建一个视图，这样我就不必在多个地方包含庞大的查询。

但是在视图中使用这个查询有一个缺点，就是每次执行视图时都必须解析JSON .因此，我正在考虑创建一个https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views，以获得这样的优势，即如果底层表数据发生变化，视图本身就必须重新执行。

不幸的是，索引视图具有相当多的先决条件。是其中之一，不允许子查询。

因此可以创建视图..。

CREATE VIEW dbo.Cities_IndexedView
WITH SCHEMABINDING
AS
    SELECT x.[CityName], x.[ResidentCount]
    FROM
        OPENJSON((SELECT [CitiesJson] FROM dbo.CitiesTable), '$.cities') 
        WITH 
            (
                [CityName] [NVARCHAR] (10) '$.cityName',
                [ResidentCount] [INT] '$.residentCount'
            ) AS x

但是，以下索引创建失败：

CREATE UNIQUE CLUSTERED INDEX Cities_IndexedView_ucidx 
    ON dbo.Cities_IndexedView([CityName]);

“无法在视图MyTestDb.dbo.Cities_IndexedView上创建索引”，因为它包含一个或多个子查询。请考虑将视图更改为只使用联接而不是子查询。或者，考虑不对此视图进行索引。

有办法解决这个问题吗？我不知道如何在不使用子选择的情况下访问CitiesJson列.

编辑：

佐洛夫有一个很好的方法来消除子查询：

SELECT x.[CityName], x.[ResidentCount]
FROM [dbo].[CitiesTable] c 
CROSS APPLY OPENJSON(c.[CitiesJson], '$.cities') WITH ([CityName] [NVARCHAR] (10) '$.cityName', [ResidentCount] [INT] '$.residentCount') AS x

但是不幸的是，APPLY不能在索引视图中使用(参见这里)：

无法在视图"MyTestDb.dbo.Cities_IndexedView“上创建索引，因为它包含应用程序。考虑不为视图建立索引，或删除“应用”。

附加需求还指出，OPENXML和表值函数也是不允许的。所以我想OPENJSON在文档中还没有提到，但也不允许：

在本地，我使用Server 2016。我创建了数据库到这里来，它使用SQLServer2019。没错，OPENJSON似乎是不可能使用的：

无法在视图'fiddle_cf57a9b555f74ea1ada4c5d0d277cf95.dbo.Cities_IndexedView‘上创建索引，因为它使用OPENJSON。

Answer 1

创建索引视图只能使用从原始数据到目标视图的注入，而当查询包含子查询、外部联接或联合、INTERSECT或EXCEPT时，这种情况永远不会发生。

您必须使用另一个逻辑，如目标表和触发器。

关于要这样做的表结构，只需使用SELECT INTO创建带有或不包含主形式数据的“快照”表:：

SELECT IDENTITY(INT, 1, 1) AS JSON_ID, x.[CityName], x.[ResidentCount]
INTO MySQL_Schema.MyJSON_Data
    FROM
        OPENJSON((SELECT [CitiesJson] FROM dbo.CitiesTable), '$.cities') 
        WITH 
            (
                [CityName] [NVARCHAR] (10) '$.cityName',
                [ResidentCount] [INT] '$.residentCount'
            ) AS x

然后将JSON_ID作为主键：

ALTER MySQL_Schema.MyJSON_Data ADD PRIMARY KEY (JSON_ID);