How can I keep a computed column of a JSON version of all the data in the row?

Question

We have a requirement to return JSONs based on numerous nested tables. I thought this would be quicker if the node tables had the JSON already prepared in a computed column. Is it possible to use JSON_QUERY in a computed column or write a function to return all columns in the row as JSON? It gets rejected when I try to put it into a new computed column on the table designer. I can imagine a function that has to look up its own data instead of taking values directly from the columns, but that seems inefficient. I welcome advice or alternative methods.

Answer 1

Yes, you can use JSON_QUERY in computed column definitions. Part of your question is asking about a computed column returning a JSON representation of other column values, though, which requires more work.

For example, if you try to create the following table definition:

create table dbo.Foo (
  FooID int,
  Bar nvarchar(50),
  Baz date,
  ComputedJSON as (select FooID, Bar, Baz for json path)
);

You'll receive the following error:

Msg 1046 Level 15 State 1 Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.

To work around this you'll need to move the subquery to a user-defined function, such as:

create function dbo.FooJson(@FooID int)
returns nvarchar(max)
as
begin
  return cast((
    select FooID, Bar, Baz
    from dbo.Foo
    where FooID=@FooID
    for json path
  ) as nvarchar(max));
end
go
create table dbo.Foo (
  FooID int,
  Bar nvarchar(50),
  Baz date,
  ComputedJSON as dbo.FooJson(FooID)
);
go

Now when you execute the following:

insert dbo.Foo (FooID, Bar, Baz) values (1, N'Hello', '21-Jan-2021');
select * from dbo.Foo;

You'll see the results:

FooID	Bar	Baz	ComputedJSON
1	Hello	2021-01-21	[{"FooID":1,"Bar":"Hello","Baz":"2021-01-21"}]