Need to do a flatmap plus join

[kensowens]

Hi I am trying to turn this [ { "ID": "fffd8851-6ccf-4157-ac72-725f78e73ec7", "HomeTeam": "Los Angeles Dodgers", "AwayTeam": "Arizona Diamondbacks", "Sport": 0, "MatchTime": "2022-09-20T02:10:00", "HomePitcher": "Michael Grove", "AwayPitcher": "Zach Davies", "HomeROT": "960", "AwayROT": "959", "Odds": [ { "ID": "9d2b95c6-766a-448f-bcad-3522fdd2646e", "EventID": "fffd8851-6ccf-4157-ac72-725f78e73ec7", "OddType": "FirstInning", "MoneyLineAway": "0", "MoneyLineHome": "0", "OverLine": "265", "TotalNumber": "1.5", "UnderLine": "-380", "PointSpreadAway": "0.5", "PointSpreadHome": "-0.5", "PointSpreadAwayLine": "-275", "PointSpreadHomeLine": "200", "DrawLine": "0", "SiteID": 11, "LastUpdated": "2022-09-20T00:48:34" }, { "ID": "ef0a7c79-fe01-4358-908e-172b31d463e7", "EventID": "fffd8851-6ccf-4157-ac72-725f78e73ec7", "OddType": "FirstFiveInnings", "MoneyLineAway": "215", "MoneyLineHome": "-257", "OverLine": "-100", "TotalNumber": "4.0", "UnderLine": "-120", "PointSpreadAway": "0.5", "PointSpreadHome": "-0.5", "PointSpreadAwayLine": "135", "PointSpreadHomeLine": "-155", "DrawLine": "0", "SiteID": 11, "LastUpdated": "2022-09-20T00:48:34" }, { "ID": "fffd8851-6ccf-4157-ac72-725f78e73ec7", "EventID": "fffd8851-6ccf-4157-ac72-725f78e73ec7", "OddType": "Game", "MoneyLineAway": "232", "MoneyLineHome": "-277", "OverLine": "-101", "TotalNumber": "7.5", "UnderLine": "-118", "PointSpreadAway": "1.5", "PointSpreadHome": "-1.5", "PointSpreadAwayLine": "110", "PointSpreadHomeLine": "-130", "DrawLine": "0", "SiteID": 11, "LastUpdated": "2022-09-20T00:48:34" } ] }, { "ID": "aa8b74fd-dac8-465f-a8d4-94445bcfe58a", "HomeTeam": "Yomiuri Giants", "AwayTeam": "Tokyo Yakult Swallows", "Sport": 19, "MatchTime": "2022-09-20T09:00:00", "HomeROT": "360108", "AwayROT": "360107", "Odds": [ { "ID": "aa8b74fd-dac8-465f-a8d4-94445bcfe58a", "EventID": "aa8b74fd-dac8-465f-a8d4-94445bcfe58a", "OddType": "Game", "MoneyLineAway": "-119", "MoneyLineHome": "-110", "OverLine": "-119", "TotalNumber": "8.5", "UnderLine": "-111", "PointSpreadAway": "-1.5", "PointSpreadHome": "1.5", "PointSpreadAwayLine": "147", "PointSpreadHomeLine": "-177", "DrawLine": "0", "SiteID": 11, "LastUpdated": "2022-09-20T00:48:34" } ] }, { "ID": "caa29f6d-311f-4b5a-a30e-f9e8e609ecc5", "HomeTeam": "Seibu Lions", "AwayTeam": "Tohoku Rakuten Golden Eagles", "Sport": 19, "MatchTime": "2022-09-20T09:00:00", "HomeROT": "360114", "AwayROT": "360113", "Odds": [ { "ID": "caa29f6d-311f-4b5a-a30e-f9e8e609ecc5", "EventID": "caa29f6d-311f-4b5a-a30e-f9e8e609ecc5", "OddType": "Game", "MoneyLineAway": "113", "MoneyLineHome": "-143", "OverLine": "108", "TotalNumber": "8.0", "UnderLine": "-138", "PointSpreadAway": "1.5", "PointSpreadHome": "-1.5", "PointSpreadAwayLine": "-186", "PointSpreadHomeLine": "156", "DrawLine": "0", "SiteID": 11, "LastUpdated": "2022-09-20T00:48:34" } ] }]

into this

[ { "homeTeam": "Los Angeles Dodgers", "AwayTeam": "Arizona Diamondbacks", "MoneyLineHome": "0", "type": "FirstInning", "MoneyLineAway": "0", "MoneyLineHome": "0", "OverLine": "265", "TotalNumber": "1.5", "UnderLine": "-380", "PointSpreadAway": "0.5", "PointSpreadHome": "-0.5", "PointSpreadAwayLine": "-275", "PointSpreadHomeLine": "200" }, { "homeTeam": "Los Angeles Dodgers", "AwayTeam": "Arizona Diamondbacks", "MoneyLineHome": "-257", "type": "FirstFiveInnings", "MoneyLineAway": "215", "MoneyLineHome": "-257", "OverLine": "-100", "TotalNumber": "4.0", "UnderLine": "-120", "PointSpreadAway": "0.5", "PointSpreadHome": "-0.5", "PointSpreadAwayLine": "135", "PointSpreadHomeLine": "-155" }, { "homeTeam": "Los Angeles Dodgers", "AwayTeam": "Arizona Diamondbacks", "MoneyLineHome": "-277", "type": "Game", "MoneyLineAway": "232", "MoneyLineHome": "-277", "OverLine": "-101", "TotalNumber": "7.5", "UnderLine": "-118", "PointSpreadAway": "1.5", "PointSpreadHome": "-1.5", "PointSpreadAwayLine": "110", "PointSpreadHomeLine": "-130" }, { "homeTeam": "Yomiuri Giants", "AwayTeam": "Tokyo Yakult Swallows", "MoneyLineHome": "-110", "type": "Game", "MoneyLineAway": "-119", "MoneyLineHome": "-110", "OverLine": "-119", "TotalNumber": "8.5", "UnderLine": "-111", "PointSpreadAway": "-1.5", "PointSpreadHome": "1.5", "PointSpreadAwayLine": "147", "PointSpreadHomeLine": "-177" }, { "homeTeam": "Seibu Lions", "AwayTeam": "Tohoku Rakuten Golden Eagles", "MoneyLineHome": "-143", "type": "Game", "MoneyLineAway": "113", "MoneyLineHome": "-143", "OverLine": "108", "TotalNumber": "8.0", "UnderLine": "-138", "PointSpreadAway": "1.5", "PointSpreadHome": "-1.5", "PointSpreadAwayLine": "-186", "PointSpreadHomeLine": "156" } ]

Trying to do this in Kumologica. Can you please help?

[kensowens]

I was able to do this in dataweave with the following

%dw 2.0 import * from dw::core::Arrays output application/json var odds = payload.Odds flatMap ((item, index) -> (item)) var join1 = join(odds, payload, (odds) -> odds.EventID, (payload) -> payload.ID)

join1 map ((item, index) -> { "homeTeam": item.r.HomeTeam, "AwayTeam": item.r.AwayTeam, "MoneyLineHome": item.l.MoneyLineHome, "type": item.l.OddType, "MoneyLineAway": item.l.MoneyLineAway, "MoneyLineHome": item.l.MoneyLineHome, "OverLine": item.l.OverLine, "TotalNumber": item.l.TotalNumber, "UnderLine": item.l.UnderLine, "PointSpreadAway": item.l.PointSpreadAway, "PointSpreadHome": item.l.PointSpreadHome, "PointSpreadAwayLine": item.l.PointSpreadAwayLine, "PointSpreadHomeLine": item.l.PointSpreadHomeLine,

})

[ab73863]

Hi @kensowens , Thanks for reaching out our support team.

Based on the input you have provided and the output you expect the following expression can be used in Kumologica to achieve the expected result.

msg.payload.Odds.{
"homeTeam" : ($$.msg.payload ~> $filter(function($parentrecord){$parentrecord.ID = $.EventID})).HomeTeam,
"AwayTeam": ($$.msg.payload ~> $filter(function($parentrecord){$parentrecord.ID = $.EventID})).AwayTeam,
"MoneyLineHome": $.MoneyLineHome,
"type": $.OddType,
"MoneyLineAway": $.MoneyLineAway,
"OverLine": $.OverLine,
"TotalNumber": $.TotalNumber,
"UnderLine": $.UnderLine,
"PointSpreadAway": $.PointSpreadAway,
"PointSpreadHome": $.PointSpreadHome,
"PointSpreadAwayLine": $.PointSpreadAwayLine,
"PointSpreadHomeLine": $.PointSpreadHomeLine
}

You can test the expression in Kumologica playground . https://playground.kumologica.com/

The above expression assume that the data is under payload hence msg.payload.Odds. If the data is in someother message structure then you may change the expression accordingly. Let us know if you are facing any issue.

You may now reach out us on discord channel for connecting with our community and for a faster response. https://discord.com/invite/HrTCY8zXxM

[kensowens]

Thanks