CODEWITHSUNDEEP
jsonsql-servert-sql
James Alexander
James Alexander

Reputation: 6302

How can I combine tabular results and json representation of the row as a column in a single query?

The sql server json capabilities for querying json in t-sql and returning an entire result set as json are great but I'm looking for a 'mixed-mode' approach where I want to specify a set of columns for the result set but then also want the entire row to be converted to a json object/column.

Something like this:

select ProductId, Name, Category, ProductJson = (* for json auto) 
from Product

How can I do this?

Upvotes: 0

Views: 219

Answers (1)

anon
anon

Reputation:

One way is a correlated subquery:

SELECT ProductId, Name, Category,
  [JSON] = (SELECT * FROM dbo.Product WHERE ProductID = p.ProductID FOR JSON AUTO) 
FROM dbo.Product AS p;

Or CROSS APPLY:

SELECT p.ProductId, p.Name, p.Category, j.son
  FROM dbo.Product AS p 
  CROSS APPLY 
  (SELECT * FROM dbo.Product WHERE ProductID = p.ProductID FOR JSON AUTO) AS j(son);

Upvotes: 3

Related Questions