How to export huge JSON data from SQL Server into multiple separate rows?

user_3682373 0
Viewed 920

I am using the FOR JSON method to extract the table data in JSON format but it exports every thing in one single row. Instead, I want to have a JSON line per record, I tried using FOR JSON PATH but that doesn't work,

Below is the sample of what I am trying to do, can't paste the whole query due to privacy reasons. Please guide me if anyone has come across the same scenario

SELECT 
    orderId,
    customerid
FROM
    T
FOR JSON PATH
format sql-server sql json database
3 Answers

If you want each row to be returned separately as a new JSON object, you need to nest FOR JSON PATH like this:

SELECT 
    (SELECT
        T.orderId,
        T.customerid
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM
    T;
user_148689970

I am using the FOR JSON method to extract the table data in JSON format but it exports every thing in one single row.

No it doesn't. It's not one row per JSON doc, as you are creating one large JSON doc, but the data is actually broken over multiple rows. SSMS just puts it back together for display.

If you want one JSON doc per row, see @Charlieface's answer.

user_72977000

SSMS is not handling JSON properly.

You should use Azure Data Studio for that. It is installed together with the latest SSMS 18.8. Or independently, on its own.

Download and install Azure Data Studio

File -> Save will produce a nicely formatted and indented JSON file.

user_19323110
Related