我有一个来自父表、子表和关系表的结果。我需要根据使用"ParentId"关系表中的值将三个表合并在一起。和";ChildId"。我曾尝试在AnyPoint Studio中使用Scatter Gather,并可以获得每个查询的结果,但我有麻烦将它们组合回一个有效载荷,以便父,关系值和子都在同一行。父母可以有一个以上的孩子。
负载基于Salesforce表的查询-
负载对象0:
Array<object>:
[
{"ParentId": "0cE3S0000000HVGUA2",
"Name": "job's Pharmacy"
"Address": "123 4th Ave",
"ParentLicense": "PHARM.PF.12345678"}
]
有效载荷对象1:
Array<Object>:
[
{ "ChildId": "0cE3S0000000GscUAE",
"ParentId": 0cE3S0000000HVGUA2,
"RelationStatus":"Site Branch"}
]
负载对象2:
Array<Object>:
[
{ "ChildId": "0cE3S0000000GscUAE",
"ChildLicense": PHRM.PF.98765432-MHOUT,
"ExpirationDate": "2019-02-28",
"PublicStatusDescription": "Closed"}
]
预期的结果
[
{
"ParentId": "0cE3S0000000HVGUA2",
"Name": "job's Pharmacy"
"Address": "123 4th Ave",
"ParentLicense": "PHARM.PF.12345678",
"ChildId": "0cE3S0000000GscUAE",
"ChildLicense": PHRM.PF.98765432-MHOUT,
"ExpirationDate": "2019-02-28",
"PublicStatusDescription": "Closed",
"RelationStatus":"Site Branch"
}
]
如果数据来自同一个数据库和同一个应用程序中的SQL查询,那么最好使用SQL来获取已经连接的数据。SQL将更有效,它基本上是设计来做到这一点。
如果数据来自不同的来源,比如不同的REST api,那么您可以使用DataWeave来连接数据。
请注意,为了简单起见,我使用JSON作为输出格式,但如果您打算进行进一步的转换,则应使用application/java。
这是一个例子。这可能不是最有效的方法:
%dw 2.0
output application/json
import * from dw::core::Arrays
var parents=payload."0"
var childs=payload."2"
var relation=payload."1"
var parentWithChildId=leftJoin(parents, relation, (p) -> p.ParentId, (r) -> r.ParentId)
map((item, index) -> item.l ++ {ChildId: item.r.ChildId})
var parentWithChildValues=leftJoin(parentWithChildId, childs, (p) -> p.ChildId, (c) -> c.ChildId)
map((item, index) -> item.l ++ (item.r - "ChildId"))
---
parentWithChildValues
输入:我不得不猜测输入应该是什么样子,假设它是一个分散收集的输出。
{
"0":
[
{
"ParentId": "0cE3S0000000HVGUA2",
"Name": "job's Pharmacy",
"Address": "123 4th Ave",
"ParentLicense": "PHARM.PF.12345678"
}
],
"1":
[
{
"ChildId": "0cE3S0000000GscUAE",
"ParentId": "0cE3S0000000HVGUA2",
"RelationStatus":"Site Branch"
}
],
"2":
[
{
"ChildId": "0cE3S0000000GscUAE",
"ChildLicense": "PHRM.PF.98765432-MHOUT",
"ExpirationDate": "2019-02-28",
"PublicStatusDescription": "Closed"
}
]
}
输出:
[
{
"ParentId": "0cE3S0000000HVGUA2",
"Name": "job's Pharmacy",
"Address": "123 4th Ave",
"ParentLicense": "PHARM.PF.12345678",
"ChildId": "0cE3S0000000GscUAE",
"ChildLicense": "PHRM.PF.98765432-MHOUT",
"ExpirationDate": "2019-02-28",
"PublicStatusDescription": "Closed"
}
]