jq map values from different arrays

Question

I want to parse the following json array that comes out of AWS GetMetricData API. It contains the timestamps and values in different arrays:

{
    "Messages": [],
    "MetricDataResults": [
        {
            "Timestamps": [
                "2021-12-28T02:11:00Z",
                "2021-12-28T02:10:00Z",
                "2021-12-28T02:09:00Z",
                "2021-12-28T02:08:00Z",
                "2021-12-28T02:07:00Z",
                "2021-12-28T02:06:00Z",
                "2021-12-28T02:05:00Z",
                "2021-12-28T02:04:00Z",
                "2021-12-28T02:03:00Z",
                "2021-12-28T02:02:00Z",
                "2021-12-28T02:01:00Z",
                "2021-12-28T02:00:00Z",
                "2021-12-28T01:59:00Z",
                "2021-12-28T01:58:00Z",
                "2021-12-28T01:57:00Z",
                "2021-12-28T01:56:00Z",
                "2021-12-28T01:55:00Z",
                "2021-12-28T01:54:00Z",
                "2021-12-28T01:53:00Z",
                "2021-12-28T01:52:00Z",
                "2021-12-28T01:51:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                5.0,
                2.0,
                2.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                0.0
            ],
            "Id": "groupDesiredCapacity",
            "Label": "myRequestLabel"
        },
        {
            "Timestamps": [
                "2021-12-28T02:11:00Z",
                "2021-12-28T02:10:00Z",
                "2021-12-28T02:09:00Z",
                "2021-12-28T02:08:00Z",
                "2021-12-28T02:07:00Z",
                "2021-12-28T02:06:00Z",
                "2021-12-28T02:05:00Z",
                "2021-12-28T02:04:00Z",
                "2021-12-28T02:03:00Z",
                "2021-12-28T02:02:00Z",
                "2021-12-28T02:01:00Z",
                "2021-12-28T02:00:00Z",
                "2021-12-28T01:59:00Z",
                "2021-12-28T01:58:00Z",
                "2021-12-28T01:57:00Z",
                "2021-12-28T01:56:00Z",
                "2021-12-28T01:55:00Z",
                "2021-12-28T01:54:00Z",
                "2021-12-28T01:53:00Z",
                "2021-12-28T01:52:00Z",
                "2021-12-28T01:51:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                5.0,
                2.0,
                2.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                1.0,
                0.0
            ],
            "Id": "groupInServiceCapacity",
            "Label": "myRequestLabel"
        },
        {
            "Timestamps": [
                "2021-12-28T02:11:00Z",
                "2021-12-28T02:10:00Z",
                "2021-12-28T02:09:00Z",
                "2021-12-28T02:08:00Z",
                "2021-12-28T02:07:00Z",
                "2021-12-28T02:06:00Z",
                "2021-12-28T02:05:00Z",
                "2021-12-28T02:04:00Z",
                "2021-12-28T02:03:00Z",
                "2021-12-28T02:02:00Z",
                "2021-12-28T02:01:00Z",
                "2021-12-28T02:00:00Z",
                "2021-12-28T01:59:00Z",
                "2021-12-28T01:58:00Z",
                "2021-12-28T01:57:00Z",
                "2021-12-28T01:56:00Z",
                "2021-12-28T01:55:00Z",
                "2021-12-28T01:54:00Z",
                "2021-12-28T01:53:00Z",
                "2021-12-28T01:52:00Z"
            ],
            "StatusCode": "Complete",
            "Values": [
                94.20519316022799,
                97.325,
                99.95833333333333,
                99.94166666666666,
                97.35833333333333,
                62.375,
                96.61666666666666,
                0.3916666666666666,
                0.3833525009583812,
                0.391647084312451,
                24.05873431223854,
                60.84898585023583,
                64.54059099015015,
                25.07541792363206,
                0.25,
                0.2499958334027766,
                0.28333333333333327,
                0.4000066667777796,
                58.31569473842103,
                5.98135840794e-05
            ],
            "Id": "cpuUtilization",
            "Label": "myRequestLabel"
        }
    ]
}

I'm trying to transform it to a table that looks like this:

Timestamp | Value | Id
2021-12-28T02:11:00Z | 5.0 | groupDesiredCapacity

I googled around and came up with this jq query .MetricDataResults[] | {t: .Timestamps[], v: .Values[],i: .Id}|[.t,.v,.i]|@csv but it seems to "multiply" the values instead of mapping them.

A bit stuck on this one, any ideas/help are appreciated.

pmf · Accepted Answer · 2021-12-28 05:52:47Z

6

Use the transpose builtin to match the array members to each other

jq -r '.MetricDataResults[] | ([.Timestamps, .Values] | transpose[]) + [.Id] | @csv'

"2021-12-28T02:11:00Z",5,"groupDesiredCapacity"
"2021-12-28T02:10:00Z",2,"groupDesiredCapacity"
"2021-12-28T02:09:00Z",2,"groupDesiredCapacity"
...
"2021-12-28T01:54:00Z",0.4000066667777796,"cpuUtilization"
"2021-12-28T01:53:00Z",58.31569473842103,"cpuUtilization"
"2021-12-28T01:52:00Z",5.98135840794e-05,"cpuUtilization"

Demo

If you want to have your desired output format instead of csv, replace @csv with join(" | ")

jq -r '.MetricDataResults[] | ([.Timestamps, .Values] | transpose[]) + [.Id] | join(" | ")'

2021-12-28T02:11:00Z | 5 | groupDesiredCapacity
2021-12-28T02:10:00Z | 2 | groupDesiredCapacity
2021-12-28T02:09:00Z | 2 | groupDesiredCapacity
...
2021-12-28T01:54:00Z | 0.4000066667777796 | cpuUtilization
2021-12-28T01:53:00Z | 58.31569473842103 | cpuUtilization
2021-12-28T01:52:00Z | 5.98135840794e-05 | cpuUtilization

Demo

edited Dec 28, 2021 at 5:52

answered Dec 28, 2021 at 5:47

pmf

38.4k3 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pmf Over a year ago

This assumes that both the .Timestamps and the .Values array have the same length (otherwise, i.e. if one is shorter than the other, transpose will fill the gaps with null values). If in that case you want to reduce the number of lines output to the (potentially smaller) number of entries in the .Timestamps array, go with @peak's solution.

GreenOak Over a year ago

Thanks, that's exactly what I needed!

peak · Accepted Answer · 2021-12-28 05:51:12Z

3

A straightforward alternative:

.MetricDataResults[]
| .Id as $Id
| range(0; .Timestamps|length) as $i
| [.Timestamps[$i], .Values[$i], $Id]
| @csv

answered Dec 28, 2021 at 5:51

peak

119k21 gold badges185 silver badges218 bronze badges

Collectives™ on Stack Overflow

jq map values from different arrays

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related