请耐心等待 - 示例代码已经发生了一些事情，我将尽我所能在下面解释它。
with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
select [propName] as entityName, [propValue] as propertyValue from (values ('A','1'),('A','2'),('B','3'),('UNIVERSAL','999')) Y([propName], [propValue])
),
SUBPROPERTIES as (
select [propValue] as propertyValue, [subPropValue] as subPropertyValue from (values ('1','x'),('1','y'),('2','z'),('999','xyz')) Y([propValue], [subPropValue])
)
--select * from ENTITIES
--select * from PROPERTIES
select (
select entityName as 'entityName',
(
select * from
(
select propertyValue as 'propertyValue',
(
select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
FOR JSON PATH, INCLUDE_NULL_VALUES
) Y
from PROPERTIES where PROPERTIES.entityName = ENTITIES.entityName
-- For testing, comment out from here -------------------------------------------------------------------------------------
UNION
select propertyValue as 'propertyValue',
(
select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
FOR JSON PATH, INCLUDE_NULL_VALUES
) Y
from PROPERTIES where PROPERTIES.entityName = 'UNIVERSAL'
-- For testing, stop commenting out here ----------------------------------------------------------------------------------
) X
FOR JSON PATH, INCLUDE_NULL_VALUES
) as entityProperties
from ENTITIES
FOR JSON PATH, INCLUDE_NULL_VALUES
) jsondata
我设置了 3 个 CTE - 代表 3 个相互链接的表。一个实体记录（如“A”）可以有多个属性（如“1”和“2”），每个属性记录可以有多个子属性（如“1”有子属性“x”和“y”）。
现在主查询只是尝试构建一个 JSON 对象，该对象将返回所有实体及其所有链接的属性和链接的子属性。
所以第一个选择列只给你 entityName - 一个顶级查询。
下一列是子查询，用于获取与给定 ENTITIES 记录相关的所有 PROPERTIES 值。您会看到它返回带有别名“propertyValue”的东西——当然这将是一组值——链接到当前实体记录的每个 PROPERTIES 记录的一个值。
子查询有第二列，它是另一个子查询 - 到 SUBPROPERTIES 表 - 类似于上面。
现在问题来了——在这个中间级别（报告属性），我实际上想从 UNION 获取数据。
（在这个例子中，联合中的第二个查询是查询同一个源表——所以我可以通过改变 WHERE 子句来避免联合or PROPERTIES.entityName = 'UNIVERSAL'——但在现实世界中，我从两个不同的表中获取数据，所以我想与 UNION 聚合）。
我知道，当FOR XML PATH与 UNION 一起使用时，您需要将所有 UNION-ed SELECT 包装在括号中并给它一个别名（在本例中为 X），然后从中选择 - 我已经用select * from. （忽略这select *是不好的做法 - 这不是重点）。
这种对 UNION 的方法很好用——只要 UNION-ed 的查询不包含子查询——但正如您在此处看到的，我有子查询。
所以 - 看看我试图说明什么，如果您注释掉 UNION（和第二个选择），您将根据下图获得结构良好的 JSON。我已经强调了在结构化 JSON 中正确显示 SUBPROPERTIES 的位置。
但是，当我添加 UNION 时，这些子查询返回字符串，而不是 JSON，如下图所示。我已经突出显示了字符串的位置。
我尝试了各种方法来添加 JSON_QUERY 函数（在某些情况下建议将字符串解析为 JSON），但是 a) 未能解决问题，并且 b) 没有实际需要完全相同的数据时存在不是联合。
我也尝试过各种方法来select * from处理最低级别的子查询 - 无济于事。
提前致谢。
问题似乎是 SQL 在合并时将内部 SELECT 中的伪字段视为文本而不是 JSON。当外部 FOR JSON 应用于此伪字段时，它会尝试转义文本字段中的特殊字符。有关更多信息，请参阅此链接。
您可以使用JSON_QUERY函数来否定这一点，本质上是强制 SQL Server 将 JSON 字段视为 JSON 而不是文本。请参阅此小提琴以获取工作示例。
查询（注意两次使用 JSON_QUERY，每个内部 SELECT 一次）：
结果