Using SQL Server Indexed View in combination with OPENJSON

Question

I have a table with just one row and one column which stores a JSON array with about 30MB/16k objects in it:

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}]}')

I use this query to parse the JSON and bring it into a relational structure:

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

Which yields:

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

I'd like to create a view for this so that I don't have to include the bulky query in several places.

But using this query inside a view has the downside that the JSON has to be parsed each time the view is executed... So I'm considering to create an Indexed View to gain the advantage that the view itself just has to be re-executed if the underlying table-data changes.

Unfortunately an indexed view has quite some prerequisites. Being one of them that no subqueries are allowed.

Hence the view can be created...

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

But the following index creation fails:

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

Cannot create index on view MyTestDb.dbo.Cities_IndexedView" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.

Is there any way to work around this? I don't know how to access the CitiesJson column within the OPENJSON without using a sub-select...

EDIT:

Zhorov had a nice idea to eliminate the subquery:

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

But unfortunately APPLY can't be used in indexed views (see here):

Cannot create index on view "MyTestDb.dbo.Cities_IndexedView" because it contains an APPLY. Consider not indexing the view, or removing APPLY.

The additional requirements also state that OPENXML and table valued functions aren't allowed either. So I guess OPENJSON is just not yet mentioned in the docs but isn't allowed as well :-(

Locally I use SQL Server 2016. I created db fiddle over here which uses SQL Server 2019. And yep, OPENJSON just seems to be impossible to use:

Cannot create index on the view 'fiddle_cf57a9b555f74ea1ada4c5d0d277cf95.dbo.Cities_IndexedView' because it uses OPENJSON.

Answer 1

Creating an indexed view can only use a sujection from the original data to the target view, which is never the case when the query contains subqueries, outer join or UNION, INTERSECT or EXCEPT.

You must use another logic like a target table and a trigger.

About the table structure to do that, just use the SELECT INTO to create the "snapshot" table with or without primary formal data ::

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

Then make JSON_ID a primary key :

ALTER MySQL_Schema.MyJSON_Data ADD PRIMARY KEY (JSON_ID);