Combine different CTE json objects together

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.

Needed Results:

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

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?

-- 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;  

Here is the data from the files used in the above:

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     

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.)

{
    "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"
                    }
                ]
            }
        }
    ]
}

Answer

Just a demo how it should work.

WITH 
-- Your sample data
  PADWOH (workOrderID, serviceAddressID, routeID) AS 
(
VALUES
  ('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) AS 
(
VALUES
  ('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')
)
-- End of your sample data

, PADWOD2 AS 
(
SELECT 
  workOrderID
, JSON_OBJECT
  (
    'workOrdersubTypeID' VALUE subTypeID
  , 'instructions'       VALUE instructions
  , 'pricePerService'    VALUE pricePerService 
  , 'serviceQuantity'    VALUE serviceQuantity
  ) subtypes
FROM PADWOD
)
, PADWOH2 AS
(
SELECT 
  workOrderID
, JSON_OBJECT
(
  'workOrderID'       VALUE workOrderID
, 'serviceAddressID'  VALUE serviceAddressID
, 'routeID'           VALUE routeID
, 'workOrderSubTypes' VALUE JSON_OBJECT 
  (
    'workOrderSubTypeList' VALUE JSON_ARRAY 
    (
      (
      SELECT subtypes 
      FROM PADWOD2 D
      WHERE D.workOrderID = H.workOrderID
      FETCH FIRST 10000 ROW ONLY
      ) FORMAT JSON
    ) FORMAT JSON
  ) FORMAT JSON
) WORKORDER 
FROM PADWOH H
FETCH FIRST 1 ROW ONLY
)

VALUES JSON_OBJECT
(
  'header'        VALUE JSON_OBJECT('InstanceName' VALUE 'US') FORMAT JSON
, 'workOrderList' VALUE JSON_ARRAY((SELECT WORKORDER FROM PADWOH2) FORMAT JSON) FORMAT JSON
);

The query works as is, but it seems that the JSON_ARRAY scalar function is broken at the moment (in 11.5.4.0 & 11.1.4.5), so you need to use these FETCH FIRST clauses (it must work without them) just to get some result (you get various SQLCODE=-901 errors otherwise).
You may open a Case with IBM Support to make it work as needed…

Update

It’s expected, that these bugs will be resolved in upcoming fixpacks for both Db2 versions.