Vtyjkyuk

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.