sqlsql-servert-sqlfor-json

How to select multiple rows as multiple rows using for json path


If I do this:

SELECT *
FROM someTable
FOR JSON PATH

I get a single result.

I want each row of the table to output as a separate row containing the json for just that row.

Can that be done?


Solution

  • You can do this as a nested subquery. WITHOUT_ARRAY_WRAPPER will remove the [] around the JSON also

    SELECT
      json = (
        SELECT p.*
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      )
    FROM someTable p;