Home > database >  How to perform group-by over JSON data using JSONATA
How to perform group-by over JSON data using JSONATA

Time:09-28

I have a JSON structure given below. I want to perform a couple of transformations on this data. I came to know about JSONATA and it looks fantastic to me, but since I am new, I am struggling a little bit around transformation. I want to calculate the total of count (sum) for each module date for the given date. The expected result is given below.

Input:

{
    "id": "6332acbfe13e6063dcb740ef",
    "record": [
        {
            "date": "2022-09-22",
            "entries": [
                {
                    "repo": "SRE-MAC-PDM-TEAM",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 16
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBO-BONUS-PAYMENTS-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANAPostPurchase",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 17
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANA-SubscriptionsRewards",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 8
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 8
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 4
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-CSP-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 9
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOIP-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 6
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOLIFE-REPO",
                    "preprod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 2
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 3
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 5
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-PL-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 4
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 2
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-TPS-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/calculated_service_metric",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/anomalies/custom/metric_id",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 11
                        }
                    ]
                }
            ]
        },
        {
            "date": "2022-09-19",
            "entries": [
                {
                    "repo": "SRE-MAC-PDM-TEAM",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 14
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBO-BONUS-PAYMENTS-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANAPostPurchase",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 15
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANA-SubscriptionsRewards",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 5
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 5
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-CSP-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 6
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOIP-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 6
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOLIFE-REPO",
                    "preprod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 2
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 3
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 4
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-PL-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 3
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 2
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-TPS-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/calculated_service_metric",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/anomalies/custom/metric_id",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 9
                        }
                    ]
                }
            ]
        },
        {
            "date": "2022-09-12",
            "entries": [
                {
                    "repo": "SRE-MAC-PDM-TEAM",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 4
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBO-BONUS-PAYMENTS-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 2
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANAPostPurchase",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANA-SubscriptionsRewards",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 3
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 4
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 2
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-CSP-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 4
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOIP-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOLIFE-REPO",
                    "preprod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 2
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 2
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 3
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-PL-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 2
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 2
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-TPS-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/calculated_service_metric",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/anomalies/custom/metric_id",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 7
                        }
                    ]
                }
            ]
        },
        {
            "date": "2022-09-05",
            "entries": [
                {
                    "repo": "SRE-MAC-PDM-TEAM",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 12
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBO-BONUS-PAYMENTS-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANAPostPurchase",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 11
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-ANA-SubscriptionsRewards",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 2
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 5
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/monitors/http-monitors/basic",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-CSP-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 5
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOIP-REPO",
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 3
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-IBOLIFE-REPO",
                    "preprod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 1
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace",
                            "count": 2
                        },
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-PL-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/splunk/hec-token",
                            "count": 1
                        },
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ]
                },
                {
                    "repo": "SRE-MAC-TPS-APPLICATION",
                    "preprod": [
                        {
                            "name": "modules/aws/lambda/logs_streaming_splunk",
                            "count": 1
                        }
                    ],
                    "prod": [
                        {
                            "name": "modules/dynatrace/alerting_profiles",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/notifications/splunk-on-call",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/calculated_service_metric",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace/anomalies/custom/metric_id",
                            "count": 1
                        },
                        {
                            "name": "modules/dynatrace",
                            "count": 2
                        }
                    ]
                }
            ]
        }
    ],
    "metadata": {
        "name": "",
        "readCountRemaining": 98,
        "timeToExpire": 86389,
        "createdAt": "2022-09-27T07:56:47.003Z"
    }
}

Expected Output:

[
"2022-09-22" : [
{
"name" : "modules/dynatrace",
"count": 11
},
{
"name" : "modules/dynatrace",
"count": 4
},
{
"name" : "modules/splunk/hec-token",
"count": 14
}
....
],
"2022-09-19" : [
{
"name" : "modules/dynatrace",
"count": 52
},
.....
]
.
.
.
]

CodePudding user response:

This should do the grouping for you:

(
  record{
    date: $reduce(entries.prod.name, function($acc, $item) {
      $merge([$acc, { 
        $item: $count(entries.prod.name[$=$item])
      }])
    }, {}) ~> $each(function($value, $key) {
      { "name": $key, "count": $value }
    })
  }
)

You can check it out live here: https://stedi.link/LiOttdB

UPDATE - even shorter solution:

(
  record{
    date: entries.*{ $.name: $sum($.count) }
      ~> $each(function($c, $n) {{ "name": $n, "count": $c }})
  }
)

https://stedi.link/pQiCnJo

  • Related