How to Format Query Results as JSON in MSSQL

One of the top features of MSSQL server 2016 is built in support of JSON. Yes, now we can format query results as JSON in MSSQL 2016 version onwards.

The data from the MSSQL can be exported to JSON by adding the FOR JSON clause into a SELECT statement. When we use the FOR JSON clause in the SELECT statement, we can specify the structure of the output explicitly, or let the structure of the SELECT statement determine the output.

  • Use PATH mode with the FOR JSON clause.

    When you use PATH mode with the FOR JSON clause, you maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.

  • Use AUTO mode with the FOR JSON clause.

    When you use AUTO mode with the FOR JSON clause, the JSON output is formatted automatically based on the structure of the SELECT statement.

Using PATH mode with the FOR JSON Clause.

To maintain full control over the format of the JSON output, specify the PATH option with the FOR JSON clause. PATH mode lets you create wrapper objects and nest complex properties.When you simply select rows from a table, the results are formatted as an array of JSON objects.If you query two or more tables, PATH mode returns flat results by default. Each column in the results becomes a property of the JSON object.

Format nested results by using dot-separated column names or by using nested queries, as shown in the following examples. By default, null values are not included in the output

Output of FOR JSON with Path mode

Use AUTO mode with the FOR JSON clause.

To format the JSON output automatically based on the structure of the SELECT statement, specify the AUTO option with the FOR JSON clause.A query that uses the FOR JSON AUTO option must have a FROM clause.With the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can’t change this format

Output of FOR JSON with Auto mode

If you need the output with root node check out the article on How to add root node to JSON output in MSSQL

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.