FOR JSON EXPLICIT

FOR JSON EXPLICIT

FOR JSON EXPLICIT 使用列别名提供详细的格式说明。如果不存在别名，将以值的形式输出指定列。必须存在别名才能表示嵌套结构内的值（或对象）。

必须将选择列表中的前两列命名为 tag 和 parent。通过指定每个查询内的标记和父项关系，多个查询的联合可返回嵌套的 JSON 输出。

别名指令的格式为 [encapsulating_object!tag!name!qualifier]，其中：

! 用于分隔指令条件。
encapsulating_object 用于为选择列表项发出封装（数组）对象。
tag 用于为后续查询中使用的列定义标识符。它还会建立嵌套条件（相对于其父项）。
name 用于为（名称/值对）对象指派名称。
qualifier 可以是 element（缺省值）或 hide，以对结果集中的元素进行模糊处理。

以下查询使用 FOR JSON EXPLICIT 返回 Employees 表中的雇员信息：

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;

结果与 FOR JSON RAW 示例的结果相同：

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

以下示例返回与 FOR JSON AUTO 示例结果类似的结果：

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;

上述查询会返回以下结果：

[
  {"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"}
      ]
    }]
  }
]

除数组排序以及包含无销售订单的雇员之外，上述格式与 FOR JSON AUTO 结果不同的原因仅在于 emp 是结构数组。在 FOR JSON AUTO 中，这意味着 emp 仅具有一个对象。FOR JSON EXPLICIT 使用支持集合的数组封装。

以下示例删除 emp 封装并以值的形式返回 Region。此示例演示了 FOR JSON EXPLICIT 模式如何提供精细的格式控制，以在 RAW 和 AUTO 模式之间生成某些内容。

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;

在查询结果中，so 现在不再是对象数组，而是一个二维数组：

[
  {"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"]
    ]
  }
]

以下示例与使用 FOR JSON RAW 时类似，但 employeeID、CustomerID 和 Region 以值（而非名称/值对）的形式输出：

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;

查询返回以下结果，即生成了由 EmployeeID、CustomerID 和 Region 组成的二维数组：

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

ARRAY 构造函数 [复合]

FOR JSON EXPLICIT

语法

用法

示例

另请参见