我很难理解将两个cte json对象组合在一起的概念。来自这两者的数据由一个字段woid进行匹配，该字段在这个json中是“workorderid”。我不知道该怎么办。
需要的结果：

{
    "header": {
        "InstanceName": "string"
    },
{
    "workOrderList": [
        {
            "workOrderID": "string",
            "serviceAddressID": "string",
            "routeID": "string",
            "workOrderSubTypes": {
                "workOrderSubTypeList": [
                    {
                        "workOrderSubTypeID": "string",
                        "instructions": "string",
                        "pricePerService": "string",
                        "serviceQuantity": 0
                    }
                ]
            }
        }
    ]
}

这是迄今为止创建的，并创建了两个部分。我似乎找不到正确的方法将它们组合成一个json对象，以便在下一步中使用。我使用cte创建json的每个部分，从最内部的部分开始向外。我被困在“结合工作顺序和子类型”的点上。也许这应该在我已经创建的步骤中完成；我不确定？

-- Create work order subtype
With fSubType1 (sSubType1) as (select json_object(     
'workOrderSubTypeID' : SvcSeq,
'instructions' : trim(Instr),
'pricePerService' : InvAmt,
'serviceQuantity' : Qty)              
  FROM PADWOD)   

-- Create work order subtype array 
 , fSubType2 (sSubType2) as (values json_array  (                     
           select sSubType1  from fSubType1 format json))  

-- Create work order subtype List
 , fSubType3 (sSubType3) as ( select json_object  ('workOrderSubTypeList'  : 
        sSubType2 Format json) from fSubType2)   

-- Create work order 
, fWorkOrder1 (sWorkOrder1) as (select json_object(     
'workOrderID' : WOID,
'serviceAddressID' : trim(Acct#),
'routeID' : RouteID) 
FROM PADWOH)

--**Combine the workorder and subtypes - not sure how to do?**
, fWorkOrder2 (sWorkOrder2) as (select  json_object('workOrderList' : sworkorder1 format json), 
json_object('workOrderSubTypes' : ssubtype3)

-- Create the work order array
, fWorkOrder3 (sWorkOrder3) as (values json_array  ( 
select sWorkOrder1 from fWorkOrder1 format json))   

-- Create the header info
, fheaderData (sheaderData) as (select  json_object( 
'InstanceName' : trim(Cntry) )
from padxmlhdr
where cntry = 'US')

-- Final json
select json_object('header' : sheaderData format json, 
'workOrderData' value sWorkOrder1 format json)
from fheaderData, fWorkOrder1;

以下是上述文件中使用的数据：

PADWOH
workOrderID                           serviceAddressID        routeID   
85789003-b2af-1a3a-898a-0004ac1acb95  BRAU0244-0001           161819    
562ef003-49af-1a3d-898a-0004ac1acb95  21CM0005-0001           161812    
37a11004-f8f8-1a46-95c9-0004ac1acb95  AMER1412-0001           191114    

PADWOD
workOrderID                           subTypeID  pricePerService  serviceQuantity  instructions 
85789003-b2af-1a3a-898a-0004ac1acb95    00001             96.36             0      Test Inst02  
85789003-b2af-1a3a-898a-0004ac1acb95    00127             10.03             0      Test Inst03  
37a11004-f8f8-1a46-95c9-0004ac1acb95    00051               .00             0      Test Inst01   

PADXMLHDR
COUNTRY      
INSTANCE NAME
     US

这是预期的json（我想，我不确定子类型记录会是什么样子——或者在没有数据显示时应该是什么样子）。我认为它不会创造任何东西，但我现在不知道。）

{
    "header": {
        "InstanceName": "US"
    },
    "workOrderList": [
        {
            "workOrderID": "85789003-b2af-1a3a-898a-0004ac1acb95",
            "serviceAddressID": "BRAU0244-0001",
            "routeID": "161819",
            "workOrderSubTypes": {
                "workOrderSubTypeList": [
                    {
                        "workOrderSubTypeID": "00001",
                        "instructions": "Test Inst02",
                        "pricePerService": "96.36",
                        "serviceQuantity": "0"
                    },
                    {
                        "workOrderSubTypeID": "00127",
                        "instructions": "Test Inst03",
                        "pricePerService": "10.03",
                        "serviceQuantity": "0"
                    }
                ]
            }
        },
        {
            "workOrderID": "562ef003-49af-1a3d-898a-0004ac1acb95",
            "serviceAddressID": "21CM0005-0001",
            "routeID": "161812",
            "workOrderSubTypes": {
                "workOrderSubTypeList": [
                    {}
                ]
            }
        },
        {
            "workOrderID": "37a11004-f8f8-1a46-95c9-0004ac1acb95",
            "serviceAddressID": "AMER1412-0001",
            "routeID": "191114",
            "workOrderSubTypes": {
                "workOrderSubTypeList": [
                    {
                        "workOrderSubTypeID": "00051",
                        "instructions": "Test Inst01",
                        "pricePerService": ".00",
                        "serviceQuantity": "0"
                    }
                ]
            }
        }
    ]
}