使用 jq 从 json 过滤对象数组

使用 jq 从 json 过滤对象数组

我试图用 jq 填充下面的 json 数据,最后的预期输出。

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
            ]
        }
    }
}

我期望输出为

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

请帮忙。

答案1

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

或者,如果你喜欢俏皮话:

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

考虑到问题中的输入,结果为:

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

第一个transpose从输入生成以下中间结果:

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

为此,我们添加使用用户提供的$metric变量挑选的指标数据,给我们

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

第二个transpose将列数组转换为行数组,我们通过该数组@csv来输出 CSV 格式的数据。标题由jq表达式中的第一行创建。

相关内容