如何编写SQL语句从变量生成嵌套JSON结构

我已经写出了生成单个节点的SQL语句，但不知道如何优化语句来合并两个输出结果。现有变量声明如下：

DECLARE
  @FulfillmentOrderId BIGINT = 0,    
  @NotifyCustomer BIT = 1,
  @TrackingCompany NVARCHAR(100) = '',
  @TrackingNo NVARCHAR(100) = '';

输入1 #

SELECT            
  @NotifyCustomer AS [notify_customer],
  @TrackingCompany AS [tracking_info.company],
  @TrackingNo AS [tracking_info.number]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

输出1 #

{
  "line_items_by_fulfillment_order": [
    {
      "fulfillment_order_id": 0
    }
  ]
}

输入2 #

SELECT (
  SELECT
    @FulfillmentOrderId AS [fulfillment_order_id]
  FOR JSON PATH
) AS [line_items_by_fulfillment_order]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

输出2 #

{
  "notify_customer": true,
  "tracking_info": {
    "company": "",
    "number": ""
  }
}

期望输出 #

{
  "fulfillment": {
    "notify_customer": true,
    "tracking_info": {
      "company": "",
      "number": 0
    },
    "line_items_by_fulfillment_order": [
      {
        "fulfillment_order_id": 0
      }
    ]
  }
}

可以通过嵌套子查询的方式，将两个部分的JSON内容整合到fulfillment节点下，对应的SQL语句如下：

DECLARE
  @FulfillmentOrderId BIGINT = 0,    
  @NotifyCustomer BIT = 1,
  @TrackingCompany NVARCHAR(100) = '',
  @TrackingNo NVARCHAR(100) = '';

SELECT
  (
    SELECT
      @NotifyCustomer AS [notify_customer],
      @TrackingCompany AS [tracking_info.company],
      -- 注意：期望输出中number是数值0，若需要匹配，可将@TrackingNo改为0或做类型转换
      CAST(@TrackingNo AS BIGINT) AS [tracking_info.number],
      (
        SELECT @FulfillmentOrderId AS [fulfillment_order_id]
        FOR JSON PATH
      ) AS [line_items_by_fulfillment_order]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS [fulfillment]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

说明 #

1. 内层子查询同时整合了notify_customer、tracking_info以及嵌套的line_items_by_fulfillment_order内容
2. 外层查询将内层结果统一包装到fulfillment节点下
3. 关于tracking_info.number：期望输出中是数值0，而原变量@TrackingNo是空字符串，可根据需求调整变量值或通过CAST转换类型

内容的提问来源于stack exchange，提问作者DreamTeK