How to convert rows to JSON in TSQL select query?

Question

Following query returns whole table as single JSON string (list of JSON objects - one per row):

SELECT * FROM MyTable FOR JSON AUTO 

I need to return multiple rows where each row will be a JSON string of a signle row of the table.

For example if table fields are A,B,C output should be:

{A: <value>, B: <value>, C: <value>}
{A: <value>, B: <value>, C: <value>}
{A: <value>, B: <value>, C: <value>}
...
{A: <value>, B: <value>, C: <value>}

How to do this in TSQL ?

While it's possible to construct JSON strings by hand by concatenating strings, I'd prefer to reuse existing tool that would handle cases like quotes in values.

Solution 1

This will return json data in rows from a select statement.

DECLARE @json NVARCHAR(Max)
SET @json = (SELECT * FROM [Sales].[Customers] FOR JSON PATH, ROOT('data'))
SELECT value
FROM OPENJSON(@json,'$.data');

Solution 2

In case anyone else stumbles on this. Similar to @Waldemar but without the need for the self join in the correlated sub-query:

SELECT (SELECT A, B, C FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM MyTable

The columns A, B and C are coming from the "MyTable" in the FROM, rather than needing a second reference and an implicit self join.

Solution 3

You can try this:

SELECT (
  SELECT ID, A, B, C, D
  FROM MyTable t1
  WHERE t1.ID = t2.ID
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS column_name
FROM MyTable t2

Solution 4

You can try this.

SELECT JTBL.* FROM MyTable A
CROSS APPLY ( select A, B, C from MyTable B WHERE B.ID = A.ID FOR JSON AUTO ) JTBL (JSTXT)

View more solutions