title: Combine different CTE json objects together
body: <p>I am having difficulties grasping the concept of combining two CTE json objects together.  The data from the two are matched by a field WOID, which is the “workOrderID” in this json.  I do not know the correct way of going about this.</p>
  <p>Needed Results:</p>
  <pre><code>{
      &quot;header&quot;: {
          &quot;InstanceName&quot;: &quot;string&quot;
      },
  {
      &quot;workOrderList&quot;: [
          {
              &quot;workOrderID&quot;: &quot;string&quot;,
              &quot;serviceAddressID&quot;: &quot;string&quot;,
              &quot;routeID&quot;: &quot;string&quot;,
              &quot;workOrderSubTypes&quot;: {
                  &quot;workOrderSubTypeList&quot;: [
                      {
                          &quot;workOrderSubTypeID&quot;: &quot;string&quot;,
                          &quot;instructions&quot;: &quot;string&quot;,
                          &quot;pricePerService&quot;: &quot;string&quot;,
                          &quot;serviceQuantity&quot;: 0
                      }
                  ]
              }
          }
      ]
  }
  </code></pre>
  <p>This is what is created so far and creates both parts.  I just cannot seem to find the correct way to combine them into one json object that I can use in the next step.  I am using CTE’s to create each part of the json, starting from the most inner part outward.  I am stuck at the point of “Combine the workorder and subtypes”.  And maybe this should be done in a step that I have already created; I am not sure?</p>
  <pre><code>-- 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;  
  </code></pre>
  <p>Here is the data from the files used in the above:</p>
  <pre><code>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     
  </code></pre>
  <p>This is the expected json (I think, I am not sure what the subtype record will look like - or should look like when there is no data to show.  I would think it would not create anything, but I do not know at this time.)</p>
  <pre><code>{
      &quot;header&quot;: {
          &quot;InstanceName&quot;: &quot;US&quot;
      },
      &quot;workOrderList&quot;: [
          {
              &quot;workOrderID&quot;: &quot;85789003-b2af-1a3a-898a-0004ac1acb95&quot;,
              &quot;serviceAddressID&quot;: &quot;BRAU0244-0001&quot;,
              &quot;routeID&quot;: &quot;161819&quot;,
              &quot;workOrderSubTypes&quot;: {
                  &quot;workOrderSubTypeList&quot;: [
                      {
                          &quot;workOrderSubTypeID&quot;: &quot;00001&quot;,
                          &quot;instructions&quot;: &quot;Test Inst02&quot;,
                          &quot;pricePerService&quot;: &quot;96.36&quot;,
                          &quot;serviceQuantity&quot;: &quot;0&quot;
                      },
                      {
                          &quot;workOrderSubTypeID&quot;: &quot;00127&quot;,
                          &quot;instructions&quot;: &quot;Test Inst03&quot;,
                          &quot;pricePerService&quot;: &quot;10.03&quot;,
                          &quot;serviceQuantity&quot;: &quot;0&quot;
                      }
                  ]
              }
          },
          {
              &quot;workOrderID&quot;: &quot;562ef003-49af-1a3d-898a-0004ac1acb95&quot;,
              &quot;serviceAddressID&quot;: &quot;21CM0005-0001&quot;,
              &quot;routeID&quot;: &quot;161812&quot;,
              &quot;workOrderSubTypes&quot;: {
                  &quot;workOrderSubTypeList&quot;: [
                      {}
                  ]
              }
          },
          {
              &quot;workOrderID&quot;: &quot;37a11004-f8f8-1a46-95c9-0004ac1acb95&quot;,
              &quot;serviceAddressID&quot;: &quot;AMER1412-0001&quot;,
              &quot;routeID&quot;: &quot;191114&quot;,
              &quot;workOrderSubTypes&quot;: {
                  &quot;workOrderSubTypeList&quot;: [
                      {
                          &quot;workOrderSubTypeID&quot;: &quot;00051&quot;,
                          &quot;instructions&quot;: &quot;Test Inst01&quot;,
                          &quot;pricePerService&quot;: &quot;.00&quot;,
                          &quot;serviceQuantity&quot;: &quot;0&quot;
                      }
                  ]
              }
          }
      ]
  }
  </code></pre>