FOR JSON Statement

JavaScript Object Notation (JSON) is a language-independent, text-based data interchange format developed for the serialization of JavaScript data. JSON also represents two structured types: objects and arrays. For more details about JSON format, see http://www.json.org

Syntax

...For JSON mode

mode:
   RAW | AUTO | EXPLICIT

Parameters

mode – controls the output format

RAW – returns query results as a flattened JSON representation. Although this mode is more verbose, it can be easier to parse. The RAW clause is the recommended method for retrieving query results as JSON objects as it is the easiest method to parse and understand.

AUTO – returns query results as nested JSON objects, based on query joins. Use the AUTO clause in a query when you want the result set to show the hierarchical relationship between the JSON objects.

EXPLICIT – allows you to specify how column data is represented. You can specify columns as simple values, objects, or nested objects to produce uniform or heterogeneous arrays.
The EXPLICIT clause uses a column alias to provide a detailed format specification. If an alias is not present, then the given column is output as a value. An alias must be present to express a value (or object) within a nested structure. You must name the first two columns in the select-list tag and parent. A union of multiple queries can return nested JSON output by specifying the tag and parent relationship within each query.

Examples

Example 1 – use JSON RAW to return employee information from the Employees table:

SELECT
   emp.EmployeeID,
   so.CustomerID,
   so.Region
FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195
ORDER BY 1
FOR JSON RAW;

Example 2 – unlike the results returned if using FOR JSON AUTO, which would hierarchically nest the results, using FOR JSON RAW returns a flattened result set:

[
   { "EmployeeID" : 129, "CustomerID" : 107, "Region" : "Eastern" },
   { "EmployeeID" : 129, "CustomerID" : 119, "Region" : "Western" },
   ...
   { "EmployeeID" : 129, "CustomerID" : 131, "Region" : "Eastern" },
   { "EmployeeID" " 195, "CustomerID" : 176, "Region" : "Eastern" }
]

Example 3 – unlike FOR JSON RAW, using FOR JSON AUTO returns a nested hierarchy of data, where an emp or Employee object is composed of an so or SalesOrders object that contains an array of CustomerID data:

[
   { "emp":
     { "EmployeeID" : 129,
     "so" : [
       { "CustomerID" : 107 , "Region" : "Eastern" },
       ...
       { "CustomerID" : 131 , "Region" : "Eastern" }
     ]
     }
   },
   { "emp" :
     { "EmployeeID" : 195,
     "so" : [
       { "CustomerID" : 109 , "Region" : "Eastern" },
       ...
       { "CustomerID" : 176 , "Region" : "Eastern" }
            ]
     }
   }
]

Example 4 – use FOR JSON EXPLICIT to return employee information from the Employees table:

SELECT
   1                AS tag,
   NULL             AS parent,
   emp.EmployeeID   AS [!1!EmployeeID],
   so.CustomerID    AS [!1!CustomerID],
   so.Region        AS [!1!Region]
FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195
ORDER BY 3
FOR JSON EXPLICIT;

Returns result identical to that of the FOR JSON RAW example:

 [
   { "EmployeeID" : 129, "CustomerID" : 107, "Region" : "Eastern" },
   { "EmployeeID" : 129, "CustomerID" : 119, "Region" : "Western" },
   ...
   { "EmployeeID" : 129, "CustomerID" : 131, "Region" : "Eastern" },
   { "EmployeeID" " 195, "CustomerID" : 176, "Region" : "Eastern" }
]

Example 5 – returns a result that is similar to the result of the FOR JSON AUTO example:

 SELECT
      1              AS tag,
      NULL           AS parent,
      emp.EmployeeID AS [emp!1!EmployeeID],
      null           AS [so!2!CustomerID],
      null           AS [!2!Region]
FROM Employees as emp where emp.EmployeeID <= 195
UNION ALL
SELECT
      2,
      1,
      emp.EmployeeID,
      so.CustomerID,
      so.Region
FROM Employees as emp KEY JOIN SalesOrders as so where emp.EmployeeID <= 195
ORDER BY 3, 1
FOR JSON EXPLICIT;

The above query returns the following result:

 [
  {"emp": [{"EmployeeID":102}]},
  {"emp":[{"EmployeeID":105}]},
  {"emp":
    [{"EmployeeID":129,
      "so":[
        {"CustomerID":101,"Region":"Eastern"},
        ...
        {"CustomerID":205,"Region":"Eastern"}
      ]
    }]
  },
  {"emp":[{"EmployeeID":148}]},
  {"emp":[{"EmployeeID":160}]},
  {"emp":[{"EmployeeID":184}]},
  {"emp":[{"EmployeeID":191}]},
  {"emp":
    [{"EmployeeID":195,
      "so":[
        {"CustomerID":101,"Region":"Eastern"},
	...
        {"CustomerID":209,"Region":"Western"}
      ]
    }]
  }
]

Besides the ordering of the arrays and the inclusion of employees with no sales orders, the format above differs from the FOR JSON AUTO results only in that emp is an array of structures. In FOR JSON AUTO it is understood that emp only has a single object. FOR JSON EXPLICIT uses an array encapsulation that supports aggregation.

The following example removes the emp encapsulation and returns Region as a value. This example demonstrates how the FOR JSON EXPLICIT mode provides a granular formatting control to produce something between the RAW and AUTO modes.

 SELECT
      1              AS tag,
      NULL           AS parent,
      emp.EmployeeID AS [!1!EmployeeID],			// remove "emp" encapsulation
      null           AS [so!2!id],				  // change "CustomerID" to just "id"
      null           AS [!2!]					   // stipulate that region should be emitted as a value
FROM Employees AS emp WHERE emp.EmployeeID <= 195
UNION ALL
SELECT
      2,
      1,
      emp.EmployeeID,
      so.CustomerID,
      so.Region
FROM Employees as emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195
ORDER BY 3, 1
FOR JSON EXPLICIT;

In the query result, so is no longer an array of objects, but is now a two-dimensional array:

 [
  {"EmployeeID":102},{"EmployeeID":105},{"EmployeeID":129,
    "so":[
      [{"id":101},"Eastern"],
      ...
      [{"id":205},"Eastern"]
    ]
  },
  {"EmployeeID":148},
  {"EmployeeID":160},
  {"EmployeeID":184},
  {"EmployeeID":191},
  {"EmployeeID":195,
    "so":[
      [{"id":101},"Eastern"],
      ...
      [{"id":209},"Western"]
    ]
  }
]

The following example is similar to using FOR JSON RAW, but employeeID, CustomerID, and Region are output as values, not name/value pairs:

 SELECT
      1               AS tag,
      NULL            AS parent,
      emp.EmployeeID,	// no alias directives
      so.CustomerID,
      so.Region
FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195
ORDER BY 3
FOR JSON EXPLICIT;

The query returns the following result, where a two-dimensional array composed of EmployeeID, CustomerID, and Region is produced:

 [
  [129,107,"Eastern"],
  ...
  [195,176,"Eastern"]
]

Usage

The FOR JSON clause can be used in any SELECT statement, including subqueries, queries with a GROUP BY clause or aggregate functions, and view definitions. Using the FOR JSON clause represents relational data as a JSON array composed of arrays, objects, and scalar elements.

The format for the alias directive is

[encapsulating_object!tag!name!qualifier]

where:

! – delimits directive criteria.
encapsulating_object – emits an encapsulating (array) object for the select-list item.
tag – defines an identifier for the column used in subsequent queries. It also establishes nesting criteria (relative to its parent).
name – assigns a name for the (name/value pair) object.
qualifier – can be either element (the default), or hide to obfuscate the element from the result set.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

None