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

Question

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?

Charlieface · Accepted Answer · 2022-10-07 11:52:06Z

1

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;

answered Oct 7, 2022 at 11:52

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

even more better - thank you

Graham · Accepted Answer · 2022-10-07 10:38:45Z

0

Never mind. I figured it out.

SELECT (SELECT *
        FROM someTable
        WHERE someUniqueColumn = p.someUniqueColumn
        FOR JSON PATH)
FROM someTable p

answered Oct 7, 2022 at 10:38

Graham

8,26122 gold badges78 silver badges127 bronze badges

SELECT (SELECT p.* FOR JSON PATH) FROM someTable p

Even better - cheers