By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[CitiesTable] ([CitiesJson] [NVARCHAR](MAX) NOT NULL) ON [PRIMARY];
INSERT INTO [dbo].[CitiesTable] ([CitiesJson]) VALUES ('{"cities":[{"cityName": "London","residentCount": 8961989},{"cityName": "Paris","residentCount": 2165423},{"cityName": "Berlin","residentCount": 3664088}]}');
1 rows affected
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 |
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]);
Msg 10148 Level 16 State 1 Line 1
Cannot create index on the view 'fiddle_cf57a9b555f74ea1ada4c5d0d277cf95.dbo.Cities_IndexedView' because it uses OPENJSON.
DROP VIEW IF EXISTS [dbo].[Cities_IndexedView];
DROP TABLE IF EXISTS [dbo].[CitiesTable];