将不同的 CTE json 对象组合在一起

Question

我很难掌握将两个 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"
}
]
}
}
]
}

Answer 1

只是一个演示它应该如何工作。

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

查询按原样工作，但目前似乎 JSON_ARRAY 标量函数已损坏(在 11.5.4.0 和 11.1.4.5 中(，因此您需要使用这些FETCH FIRST子句(它必须在没有它们的情况下工作(只是为了得到一些结果(否则你会得到各种 SQLCODE=-901 错误(.
您可以向 IBM 支持部门提交案例以使其根据需要工作...

更新

预计这些错误将在即将发布的两个 Db2 版本的修订包中得到解决。