filter array of objects using jq from json

Question

im trying to fillter the below json data with jq and expected out-put as last.

cat data.json

{
    "params": {
        "filters": {},
        "group_by": [
            "service"
        ],
        "metrics": [
            "cocekop:count",
            "cocekop.seconds_to_first_ack:avg",
            "cocekop.seconds_to_resolve:avg",
            "cocekop.escalated:count"
        ],
        "since": "2022-03-01T15:00:00.000Z",
        "time_zone": "Asia/Tokyo",
        "until": "2022-03-02T14:59:59.000Z"
    },
    "report": {
        "groups": [
            [{
                    "id": "PKOLSA",
                    "name": "Commuincation"
                },
                {
                    "id": "PRKOLS",
                    "name": "Designation"
                },
                {
                    "id": "PKDFKDL",
                    "name": "Mapping"
                }
            ]
        ],
        "metrics": {
            "cocekop.escalated:count": [
                1,
                0,
                0
            ],
            "cocekop.seconds_to_first_ack:avg": [
                86,
                0,
                9
            ],
            "cocekop.seconds_to_resolve:avg": [
                8161,
                492,
                301
            ],
            "cocekop:count": [
                1,
                1,
                0
            ]
        }
    }
}

im expecting ouput as

id,name,cocekop:count
PKOLSA,Commuincation,1
PRKOLS,Designation,1
PKDFKDL,Mapping,0

Please help.

Kusalananda · Accepted Answer · 2022-03-02 17:15:02Z

jq -r --arg metric "cocekop:count" '
[ "id", "name", $metric ],
(
        (
                [
                        .report.groups[0][] |
                        [.[]]
                ] |
                transpose
        ) +
        [
                .report.metrics[$metric]
        ] |
        transpose[]
) | @csv' file

Or, if you like one-liners:

jq -r --arg metric "cocekop:count" '["id","name",$metric],(([.report.groups[0][]|[.[]]]|transpose)+[.report.metrics[$metric]]|transpose[])|@csv' file

The result, given the input in the question:

"id","name","cocekop:count"
"PKOLSA","Commuincation",1
"PRKOLS","Designation",1
"PKDFKDL","Mapping",0

The first transpose produces the following intermediate result from the input:

[
  [
    "PKOLSA",
    "PRKOLS",
    "PKDFKDL"
  ],
  [
    "Commuincation",
    "Designation",
    "Mapping"
  ]
]

To this, we add the metric data that we pick out using the user-provided $metric variable, giving us

[
  [
    "PKOLSA",
    "PRKOLS",
    "PKDFKDL"
  ],
  [
    "Commuincation",
    "Designation",
    "Mapping"
  ],
  [
    1,
    1,
    0
  ]
]

The second transpose converts this array of columns into an array of rows that we pass through @csv to output CSV-formatted data. The header is created by the first line in the jq expression.

Thank you very much for explanation.

SRash
– SRash

2022-03-03 13:41:31 +00:00
Commented Mar 3, 2022 at 13:41 — SRash
– SRash, Commented Mar 3, 2022 at 13:41

Stack Exchange Network

filter array of objects using jq from json

1 Answer 1

You must log in to answer this question.

Hot Network Questions

filter array of objects using jq from json

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions