jq - group json objects by field value and output grouped values in one line

Question

I have a json format with metrics & timestamps & values from AWS Cloudwatch.

{
    "Messages": [],
    "MetricDataResults": [
        {
            "Timestamps": [
                "2021-07-07T13:26:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                0.0
            ],
            "Id": "m19",
            "Label": "CPUSurplusCreditsCharged"
        },
        {
            "Timestamps": [
                "2021-07-07T13:28:00Z",
                "2021-07-07T13:27:00Z",
                "2021-07-07T13:26:00Z",
                "2021-07-07T13:25:00Z",
                "2021-07-07T13:24:00Z",
                "2021-07-07T13:23:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                12.750425014167137,
                13.033116114731422,
                12.70812153130781,
                12.975,
                15.441924032067199,
                12.916451392476791
            ],
            "Id": "m20",
            "Label": "CPUUtilization"
        },
        {
            "Timestamps": [
                "2021-07-07T13:29:00Z",
                "2021-07-07T13:28:00Z",
                "2021-07-07T13:27:00Z",
                "2021-07-07T13:26:00Z",
                "2021-07-07T13:25:00Z",
                "2021-07-07T13:24:00Z",
                "2021-07-07T13:23:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                0.7,
                0.6999533364442371,
                0.6998833527745376,
                0.6999416715273727,
                0.7,
                0.7001166861143524,
                0.6998950157476379
            ],
            "Id": "m21",
            "Label": "NetworkReceiveThroughput"
        }
    ]
}

I put these values in an array variable using the jq command.
And the result is output to the array variable as follows.

jq -r '.MetricDataResults[] | "\(.Label) \(.Timestamps) \(.Values)"' test.json | while read Label timestamp value
do

  Label=`echo $Label | sed 's/\"//g; s/\[//g; s/\]//g; s/,/ /g'`
  timestamp=`echo $timestamp | sed 's/\"//g; s/\[//g; s/\]//g; s/,/ /g'`
  value=`echo $value | sed 's/\"//g; s/\[//g; s/\]//g; s/,/ /g'`

  arr_timestamp=($timestamp)
  arr_value=($value)

  echo $Label
  echo ${arr_timestamp[@]}
  echo ${arr_value[@]}
done



Evictions
2021-07-07T10:51:00Z 2021-07-07T10:50:00Z 2021-07-07T10:49:00Z 2021-07-07T10:48:00Z 2021-07-07T10:47:00Z 2021-07-07T10:46:00Z 2021-07-07T10:45:00Z
0 0 0 0 0 0 0

CPUUtilization
2021-07-07T10:50:00Z 2021-07-07T10:49:00Z 2021-07-07T10:48:00Z 2021-07-07T10:47:00Z 2021-07-07T10:46:00Z 2021-07-07T10:45:00Z
1.5333333333333332 1.4666666666666666 1.5833333333333333 1.5333333333333332 1.4916666666666665 1.4916666666666665

IsMaster
2021-07-07T10:51:00Z 2021-07-07T10:50:00Z 2021-07-07T10:49:00Z 2021-07-07T10:48:00Z 2021-07-07T10:47:00Z 2021-07-07T10:46:00Z 2021-07-07T10:45:00Z
1 1 1 1 1 1 1

When timestamp is not the same length for each array variable,
I want to display only values in the same timestamp as a single string.

For example

"2021-07-07T10:51:00Z Evictions = 0\nIsMaster = 1"
"2021-07-07T10:50:00Z Evictions = 0\nCPUUtilization = 1.5333333333333332\n IsMaster = 1"
...

My head is bad and I can't think of a good way.
Please let me know if there is any good way.
I don't have much time so please help on stackoverflow.

Add
I mean group by Timestamps. Like this

{
    "MetricDataResults": [
        {
            "Timestamps": "2021-07-07T13:28:00Z",
            "Label" : [
               "CPUUtilization",
               "NetworkReceiveThroughput"
            ],
            "Values" : [
               12.750425014167137,
               0.7
            ]
         },
         {
            "Timestamps": "2021-07-07T13:27:00Z",
            "Label" : [
               "CPUUtilization",
               "NetworkReceiveThroughput"
            ],
            "Values" : [
               13.033116114731422,
               0.6999533364442371
            ]
         },
         {
            "Timestamps": "2021-07-07T13:26:00Z",
            "Label" : [
               "CPUUtilization",
               "NetworkReceiveThroughput",
               "CPUSurplusCreditsCharged"
            ],
            "Values" : [
               12.70812153130781,
               0.6998833527745376,
               0.0
            ]
        }
    ]
}

i personally prefer python or perl(it is said perl is very powerful text extraction tool) — Lei Yang, Jul 08 '21 at 06:34
Yup, I would say that pythons `json` and `requests` modules are more suited and easy to maintain , take a look here: https://stackoverflow.com/questions/6386308/http-requests-and-json-parsing-in-python — dejanualex, Jul 08 '21 at 08:04
The task could be easily achieved using jq without messing around with shell variables, shell arrays, or sed, but your specific requirements are unclear to me. If you showed the complete expected output it would help. — peak, Jul 08 '21 at 08:06

jpseng · Answer 1 · 2021-07-08T14:09:14.163

You can achieve your goal just using jq. Further processing by shell scripting is not necessary. The following shell script gives you two alternatives:

output as text
output as json

#!/bin/bash

INPUT='
{
    "Messages": [],
    "MetricDataResults": [
        {
            "Timestamps": [
                "2021-07-07T13:26:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                0.0
            ],
            "Id": "m19",
            "Label": "CPUSurplusCreditsCharged"
        },
        {
            "Timestamps": [
                "2021-07-07T13:28:00Z",
                "2021-07-07T13:27:00Z",
                "2021-07-07T13:26:00Z",
                "2021-07-07T13:25:00Z",
                "2021-07-07T13:24:00Z",
                "2021-07-07T13:23:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                12.750425014167137,
                13.033116114731422,
                12.70812153130781,
                12.975,
                15.441924032067199,
                12.916451392476791
            ],
            "Id": "m20",
            "Label": "CPUUtilization"
        },
        {
            "Timestamps": [
                "2021-07-07T13:29:00Z",
                "2021-07-07T13:28:00Z",
                "2021-07-07T13:27:00Z",
                "2021-07-07T13:26:00Z",
                "2021-07-07T13:25:00Z",
                "2021-07-07T13:24:00Z",
                "2021-07-07T13:23:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                0.7,
                0.6999533364442371,
                0.6998833527745376,
                0.6999416715273727,
                0.7,
                0.7001166861143524,
                0.6998950157476379
            ],
            "Id": "m21",
            "Label": "NetworkReceiveThroughput"
        }
    ]
}
'

# output as plain text
jq -r '
  .MetricDataResults
  | map(.Values as $values | .Timestamps as $timestamps
        | {Label} +
          foreach range(.Timestamps | length) as $idx
                  (null; {"Timestamp": $timestamps[$idx], "Value": $values[$idx]}; .))
  | group_by(.Timestamp)[]
  | [.[0].Timestamp]
    + map("\(.Label)=\(.Value)")
    | join("\n") + "\n"
' <<< "$INPUT"

# output as json
jq -r '
  .MetricDataResults
  |= (map(.Values as $values | .Timestamps as $timestamps
          | {Id, Label, StatusCode} +
            foreach range(.Timestamps | length) as $idx
                    (null; {"Timestamp": $timestamps[$idx], "Value": $values[$idx]}; .))
     | group_by(.Timestamp)
     | map({Timestamp: .[0].Timestamp,
            Events: del(.[].Timestamp)}))
' <<< "$INPUT"

The first jq command of the shell script produces:

2021-07-07T13:23:00Z
CPUUtilization=12.916451392476791
NetworkReceiveThroughput=0.6998950157476379

2021-07-07T13:24:00Z
CPUUtilization=15.441924032067199
NetworkReceiveThroughput=0.7001166861143524

2021-07-07T13:25:00Z
CPUUtilization=12.975
NetworkReceiveThroughput=0.7

2021-07-07T13:26:00Z
CPUSurplusCreditsCharged=0
CPUUtilization=12.70812153130781
NetworkReceiveThroughput=0.6999416715273727

2021-07-07T13:27:00Z
CPUUtilization=13.033116114731422
NetworkReceiveThroughput=0.6998833527745376

2021-07-07T13:28:00Z
CPUUtilization=12.750425014167137
NetworkReceiveThroughput=0.6999533364442371

2021-07-07T13:29:00Z
NetworkReceiveThroughput=0.7

The second jq command of the shell script produces:

{
  "Messages": [],
  "MetricDataResults": [
    {
      "Timestamp": "2021-07-07T13:23:00Z",
      "Events": [
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 12.916451392476791
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.6998950157476379
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:24:00Z",
      "Events": [
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 15.441924032067199
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.7001166861143524
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:25:00Z",
      "Events": [
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 12.975
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.7
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:26:00Z",
      "Events": [
        {
          "Id": "m19",
          "Label": "CPUSurplusCreditsCharged",
          "StatusCode": "Complete",
          "Value": 0
        },
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 12.70812153130781
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.6999416715273727
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:27:00Z",
      "Events": [
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 13.033116114731422
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.6998833527745376
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:28:00Z",
      "Events": [
        {
          "Id": "m20",
          "Label": "CPUUtilization",
          "StatusCode": "Complete",
          "Value": 12.750425014167137
        },
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.6999533364442371
        }
      ]
    },
    {
      "Timestamp": "2021-07-07T13:29:00Z",
      "Events": [
        {
          "Id": "m21",
          "Label": "NetworkReceiveThroughput",
          "StatusCode": "Complete",
          "Value": 0.7
        }
      ]
    }
  ]
}

Thank you.. Thank you so much.
I ran your first jq command. The result is an error.. May I know if you executed it from a shell script file?
error message```error: syntax error, unexpected IDENT``` — P.Lonnie, Jul 08 '21 at 13:13
And I execute in shell file ```jq -r ' .MetricDataResults |= (map(.Values as $values | .Timestamps as $timestamps | {Id, Label, StatusCode} + foreach range(.Timestamps | length) as $idx (null; {"Timestamp": $timestamps[$idx], "Value": $values[$idx]}; .)) | group_by(.Timestamp) | map({Timestamp: .[0].Timestamp, Events:. | del(.[] | .Timestamp)})) ' sh-test-001.json ``` — P.Lonnie, Jul 08 '21 at 13:14
And this.. ```jq -r '.MetricDataResults | map(.Values as '$values' | .Timestamps as '$timestamps' | {Id, Label, StatusCode} + foreach range(.Timestamps | length) as '$idx' (null; {"Timestamp": '$timestamps[$idx]', "Value": '$values[$idx]'}; .)) | group_by(.Timestamp)[] | [.[0].Timestamp] + map("\(.Label)=\(.Value)") | join("\n") + "\n"' sh-test-001.json``` — P.Lonnie, Jul 08 '21 at 13:15
Ohhhh. sorry sir... My jq version was 1.4, I upgraded it to 1.6 and succeeded. Thank you so much. — P.Lonnie, Jul 08 '21 at 13:28
Yes. You are right. This problem was caused by my jq version being low. Thank you. — P.Lonnie, Jul 08 '21 at 13:38

score 3 · Accepted Answer · answered Jul 08 '21 at 17:52

3

Here is a simple, idiomatic solution for the case of text output; it can be used with any version of jq from 1.3 onwards. Note in particular that it does not rely on foreach, the use of which here is overly complicated:

< input.json jq -r '
  .MetricDataResults
  | map(.Values as $values
        | .Timestamps as $timestamps
        | {Label} +
           (range(0; .Timestamps|length) as $idx
            | {Timestamp: $timestamps[$idx], 
               Value:     $values[$idx]} ))
  | group_by(.Timestamp)[]
  | .[0].Timestamp, (.[]|"\(.Label)=\(.Value)"), ""
'

answered Jul 08 '21 at 17:52

peak

105,803
17
152
177

I thought about a `foreach`-free solution but didn't come up with it. Thank you for showing how to do it ! – jpseng Jul 09 '21 at 05:59
@jpseng - And thanks for making some sense of the Q :-) – peak Jul 09 '21 at 07:01

jq - group json objects by field value and output grouped values in one line

2 Answers2