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"
}
]
}
}
]
}
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...
It's expected, that these bugs will be resolved in upcoming fixpacks for both Db2 versions.