Forum Discussion

andre_mangatal's avatar
andre_mangatal
New Contributor
7 years ago

Help Using Group By!

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 Grou...
andre_mangatal's avatar
andre_mangatal
New Contributor
6 years ago

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's avatar
    tstack
    Former Employee
    6 years ago

    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.