SQL Server - Indexed View with FOR JSON

Viewed 528

I'm testing different ways that I can implement CQRS. One of my test uses FOR JSON queries to bypass any type of ORM, much like described here. Another implementation I wanted to test was, instead of creating the JSON data on-the-fly, to create an key-value type indexed view where the key is some identifier and the data is the JSON data created from the 'actual' data. For data stores that are updated sparingly but read frequently, this would seem like a smart thing to do. The problem is how to create the indexed view, something like the following.

CREATE TABLE [dbo].[JsonTest]
(
    [Number] [NVARCHAR](50) NULL,
    [Date] [DATETIME] NULL,
    [Customer] [NVARCHAR](50) NULL,
    [Price] [MONEY] NULL,
    [Quantity] [INT] NULL
) ON [PRIMARY]

CREATE VIEW dbo.CustomerOrders
WITH SCHEMABINDING
AS
(
    SELECT 
        customer,
        (SELECT 
             Customer AS AccountNumber,
             Number AS [Order.Number],
             Date AS [Order.Date],
             Price AS 'Item.UnitPrice',
             Quantity AS 'Item.Qty'
         FROM
             dbo.JsonTest
         WHERE
             Customer = j.Customer
         FOR JSON PATH, ROOT('Orders')) AS data
    FROM
        dbo.JsonTest j);

The problem is you can't create an indexed view from a view with subqueries. When using FOR JSON the entire query result is converted to JSON. What I need is the ability to convert the data in some columns into JSON.

0 Answers
Related