How to convert rows to JSON in TSQL select query?

问题:

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.

回答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');

回答2:

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)

回答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