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" } ] } } ] }
Advertisement
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.