Help Using Group By!

[andre_mangatal]

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

[andre_mangatal]

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

[tstack]

andre.mangatal:

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?

Can you clarify a couple of things:

• Are the date fields strings or Date objects?
• Are the elements of $group already sorted by date?

[andre_mangatal]

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”}]}]

[tstack]

andre.mangatal:

I need to return ONLY the first record in each group i.e. with the latest date.

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.