'Can a SQL Server query return or C# generate newline delimited json? [closed]

I am trying to return a data set from a SQL Server stored procedure where output should look like newline delimited json. How can I achieve this?

SELECT [ID],[Name]
FROM [tbl_sample]
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

If it is not possible from SQL Server end directly, is it possible from C#?

I mean, I need to create each SQL select dataTable to generate individual ND-JSON file in the directory.

Could you please share your thoughts it would be highly appreciated.

Thanks!

c++sharpsql-server


Solution 1:[1]

You can put each row in a subquery to create its JSON individually, then aggregate it using new-lines using STRING_AGG:

SELECT STRING_AGG(j.json, N'
') AS json
FROM [tbl_sample]
CROSS APPLY (
    SELECT [ID],[Name]
    FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
) j(json);

Alternatively you can return each row individually:

SELECT
  (
    SELECT [ID],[Name]
    FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
  ) AS json
FROM [tbl_sample];

db<>fiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions