FOR JSON EXPLICIT

FOR JSON EXPLICIT

FOR JSON EXPLICIT 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.

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.

The following query uses 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;

The result is 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" }
]

The following example 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"]
]

ARRAY constructor [Composite]

FOR JSON EXPLICIT

Syntax

Usage

Example

See also