如何用FOR JSON AUTO实现含1对1关联的多JOIN JSON输出？

问题场景 #

使用INNER JOIN关联Quote与Quote_Line表，通过FOR JSON AUTO已得到正确的嵌套JSON结果。但添加Quote与Quote_Customer的1对1关联后，customer被意外嵌套进了每个lines对象中，需要将customer作为Quote对象的平级属性输出。

现有SQL语句 #

SELECT quote.id,
       lines.id
        
FROM Quote quote

INNER JOIN Quote_Line as lines
    ON quote.id = lines.quote_id

FOR JSON AUTO

现有输出JSON #

[
  {
    "id": 1,
    "lines": [
      {
        "id": 1
      }
    ]
  }
]

期望输出JSON #

[
  {
    "id": 1,
    "customer": {
      "id": 1
    },
    "lines": [
      {
        "id": 1
      }
    ]
  }
]

解决方案 #

方法一：通过列别名层级控制（推荐） #

利用FOR JSON AUTO依据列别名的层级关系生成JSON结构的特性，将Quote_Customer的字段用[customer.xxx]的别名格式，并且放在Quote_Line字段之前，SQL引擎会自动将customer识别为Quote的平级子对象，而非lines的嵌套对象。

SELECT 
    quote.id,
    customer.id AS [customer.id], -- 用别名指定层级，放在lines字段前
    lines.id
FROM Quote quote
LEFT JOIN Quote_Customer customer 
    ON quote.id = customer.quote_id
INNER JOIN Quote_Line lines 
    ON quote.id = lines.quote_id
FOR JSON AUTO;

方法二：子查询+JSON_QUERY构造平级对象 #

对于1对1关联场景，通过子查询单独获取customer数据，用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER生成单个JSON对象，再用JSON_QUERY确保该对象被正确解析为JSON结构而非字符串。

SELECT
    quote.id,
    JSON_QUERY(
        (SELECT id FROM Quote_Customer WHERE quote_id = quote.id 
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    ) AS customer,
    lines.id
FROM Quote quote
INNER JOIN Quote_Line lines 
    ON quote.id = lines.quote_id
FOR JSON AUTO;

两种方法都能实现需求：方法一更简洁，适合关联字段较少的场景；方法二灵活性更高，适合构造复杂customer对象的场景。

内容的提问来源于stack exchange，提问作者Jonas Mærsk