Format results as JSON with FOR JSON#

Format query results as JSON, or export data from Querona as JSON, by adding the FOR JSON clause to a SELECT statement. Use the FOR JSON clause to simplify client applications by delegating the formatting of JSON output from the app to Querona.

When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.
To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

Option 1 - You control output with FOR JSON PATH#

In PATH mode, to format nested output use the dot syntax - for example, ‘Item.Price’.

Here’s a sample query that uses PATH mode with the FOR JSON clause. The following example also uses the ROOT option to specify a named root element.

Option 2 - SELECT statement controls output with FOR JSON AUTO#

In AUTO mode, the structure of the SELECT statement determines the format of the JSON output.

By default, null values aren’t included in the output. You can use INCLUDE_NULL_VALUES to change this behavior.

Here’s a sample query that uses AUTO mode with the FOR JSON clause.

SELECT name, surname
  FROM emp
   FOR JSON AUTO;

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

2.b - Example with JOIN and NULL#

The following example of SELECT…FOR JSON AUTO includes a display of what the JSON results look like when there is a 1:many relationship between data from joined tables.

The absence of the null value from the returned JSON is also illustrated. However, you can override this default behavior by use of the INCLUDE_NULL_VALUES keyword on the FOR clause.

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL,
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL,
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentGuid, StudentName, ClassGuid)
VALUES
    (newid(), 'Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    (newid(), 'Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    (newid(), 'Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    (newid(), 'Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    (newid(), 'Carla Cap', null);
GO

SELECT c.ClassName,
       s.StudentName
  FROM #tabClass AS c
 RIGHT
  JOIN #tabStudent AS s
    ON s.ClassGuid = c.ClassGuid
 ORDER
    BY c.ClassName,
       s.StudentName
   FOR JSON AUTO
       -- To include NULL values in the output, uncomment the following line:
       --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE #tabStudent;
DROP TABLE #tabClass;
GO

JSON output of the preceding SELECT.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Output of the FOR JSON clause#

The output of the FOR JSON clause has the following characteristics:

The result set contains a single column.
- A small result set may contain a single row.
- A large result set splits the long JSON string across multiple rows.
  - By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.
  - Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows.
The results are formatted as an array of JSON objects.
- The number of elements in the JSON array is equal to the number of rows in the results of the SELECT statement (before the FOR JSON clause is applied).
- Each row in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a separate JSON object in the array.
- Each column in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a property of the JSON object.
Both the names of columns and their values are escaped according to JSON syntax.

Example#

Here’s an example that demonstrates how the FOR JSON clause formats the JSON output.

Query results

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

JSON output

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Format results as JSON with FOR JSON

Contents