CODEWITHSUNDEEP
jsongroup-bystatisticsjq
d123
d123

Reputation: 1617

JSON jq grouping and average

Looking to try to group and average the following json

{
    "SpotPriceHistory": [
        {
        "AvailabilityZone": "ap-southeast-1c",
        "InstanceType": "t4g.small",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "0.001200",
        "Timestamp": "2021-03-17T18:27:08+00:00"
        },
        {
        "AvailabilityZone": "ap-southeast-1a",
        "InstanceType": "t4g.small",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "0.002400",
        "Timestamp": "2021-03-17T18:27:08+00:00"
        },
        {
        "AvailabilityZone": "ap-southeast-1b",
        "InstanceType": "t4g.small",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "0.005600",
        "Timestamp": "2021-03-17T18:27:08+00:00"
        },
        {
        "AvailabilityZone": "ap-southeast-1a",
        "InstanceType": "m1.large",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "0.033300",
        "Timestamp": "2021-03-17T18:20:33+00:00"
        },
        {
        "AvailabilityZone": "ap-southeast-1b",
        "InstanceType": "m1.large",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "0.023300",
        "Timestamp": "2021-03-17T18:20:33+00:00"
        },
        {
        "AvailabilityZone": "ap-southeast-1b",
        "InstanceType": "r5ad.24xlarge",
        "ProductDescription": "Linux/UNIX",
        "SpotPrice": "1.644400",
        "Timestamp": "2021-03-17T18:20:20+00:00"
        }
    ]
}

Looking for the output to look something like,

{
    "AvailabilityZone": "ap-southeast",
    "InstanceType": "t4g.small",
    "ProductDescription": "Linux/UNIX",
    "SpotPrice": "0.00306"
},
{
    "AvailabilityZone": "ap-southeast",
    "InstanceType": "m1.large",
    "ProductDescription": "Linux/UNIX",
    "SpotPrice": "0.0283"
},
{
    "AvailabilityZone": "ap-southeast",
    "InstanceType": "r5ad.24xlarge",
    "ProductDescription": "Linux/UNIX",
    "SpotPrice": "1.644400"
}

I've got something like .SpotPriceHistory | group_by(.InstanceType)[] to group.

Upvotes: 0

Views: 334

Answers (1)

0stone0
0stone0

Reputation: 44122

Quite sure this is not the most 'clean' solution, but thought I'd share it until there's a better approach

Idea:

  1. group_by each InstanceType
  2. map each SpotPrice, add those values by using add, devide by map length to get avarage (map(.SpotPrice | tonumber) | add / length)
.SpotPriceHistory | group_by(.InstanceType)[] | { "InstanceType": .[0].InstanceType, "SpotPrice": (map(.SpotPrice | tonumber) | add / length) }

Result:

{
  "InstanceType": "m1.large",
  "SpotPrice": 0.028300000000000002
}
{
  "InstanceType": "r5ad.24xlarge",
  "SpotPrice": 1.6444
}
{
  "InstanceType": "t4g.small",
  "SpotPrice": 0.0030666666666666668
}

Jq▷Play

Upvotes: 3

Related Questions