โ11-15-2018 06:26 AM
Dear Community
I need your help with understanding the group by in the attached simple pipeline.
I am supplying the Json data and sorting and grouping it by ID as in the picture but for each Group, i.e. ID. Then I need to first determine if a type of address exists e.g. โBUSโ business or โRESโ residential and then transform, e.g. via a mapper, to output both the business and residential addresses to Mapper variables $Business and $Residential output as one document/record
Thank you very much for your help in advance,
Andre M
โ09-25-2019 12:13 PM
Hi tstack,
In the group if I have a date field and I wanted to get the record with the latest date returned, how would I do that?
match $group {
[โฆ, { EndDate: โ2019-8-31โ, Value }, โฆ] => Value
}
Many thanks
โ09-26-2019 08:50 AM
Can you clarify a couple of things:
$group
already sorted by date?โ09-26-2019 08:59 AM
Hi tstack
Thank you for the reply. I need to get a solution for this. The date fields are strings and they are sorted descending before i did the group by via JDBC execute. A sample of the json just after the group by is shown below. I am grouping by โPOL_IDโ then โCVG_NUMโ but I need to return ONLY the first record in each group i.e. with the latest date. How can I do this please??
[
{โgroupByโ:{โCVG_NUMโ:โ01โ,โPOL_IDโ:โDT90000051โ},โgroupโ:[{โCVG_NUMโ:โ01โ,โPOL_IDโ:โDT90000051โ,โCO_IDโ:โ01โ,โFIA_EFF_DTโ:โ2010-04-30โ}]},{โgroupByโ:{โCVG_NUMโ:โ02โ,โPOL_IDโ:โDT90000051โ},โgroupโ:[{โCVG_NUMโ:โ02โ,โPOL_IDโ:โDT90000051โ,โCO_IDโ:โ01โ,โFIA_EFF_DTโ:โ2010-04-30โ}]},{โgroupByโ:{โCVG_NUMโ:โ01โ,โPOL_IDโ:โDT90000051โ}]}]
โ09-26-2019 09:19 AM
If you just need the first element of the group, you can index it directly, like so:
$group[0]
If youโre wanting to deconstruct it with a match
expression, you can match the first element by making a pattern like the following:
match $group {
[{ CO_ID, FIA_EFF_DT }, โฆ] => CO_ID + " " + FIA_EFF_DT
}
When writing a pattern for match
, write something that looks like the thing youโre trying to match and use ...
as a wildcard. So, the above pattern will match an array with at least one object element that has the given properties.