使用jq将含数组的复杂JSON转为CSV时无法提取数组值

问题场景 #

有如下包含嵌套数组的JSON结构，需要转换为CSV并提取数组内的元素：

{
  "creation_date": "2023/06/16 15:55:09",
  "data": {
    "transactionType": "val",
    "environment": "1",
    "location": {
      "zoneId": 2
    },
    "media": {
      "typeCode": 15,
      "postPaymentEvent": [
        {
          "transactionDate": "2022-12-26T16:50:00+01:00",
          "vehicleType": 1,
          "vehicleId": 1533
        },
        {
          "transactionDate": "2022-12-27T16:31:00+01:00",
          "vehicleType": 1,
          "vehicleId": 1533
        },
        {
          "transactionDate": "2022-12-28T15:50:00+01:00",
          "vehicleType": 1,
          "vehicleId": 1533
        }
      ]
    },
    "deviceConfiguration": [
      {
        "id": "PLATFORM_PAR",
        "version": "2.6"
      },
      {
        "id": "PLATFORM_SOFT",
        "version": "2.6"
      }
    ]
  },
  "id": "000-0"
}

尝试用以下jq命令提取值时，data.media.postPaymentEvent[0].transactionDate始终无法返回结果：

jq --raw-output '[ "creation_date", "data.transactionType",  "data.location.zoneId", "data.media.typeCode",  "data.media.postPaymentEvent[0].transactionDate",  "id" ] as $h | $h, (. | [getpath($h[] / ".")]) | @csv'

问题原因 #

getpath函数接收的是路径数组，原命令中用$h[] / "."拆分data.media.postPaymentEvent[0].transactionDate时，得到的是["data","media","postPaymentEvent[0]","transactionDate"]——其中postPaymentEvent[0]被当作了一个节点名称，而非数组索引，导致无法定位到数组元素。

解决方案 #

方案1：提取数组指定索引的元素（单条记录） #

将路径定义为包含数组索引的数组形式，让getpath能正确识别数组位置：

jq --raw-output '
[
  ["creation_date"],
  ["data", "transactionType"],
  ["data", "location", "zoneId"],
  ["data", "media", "typeCode"],
  ["data", "media", "postPaymentEvent", 0, "transactionDate"],
  ["id"]
] as $paths |
# 生成表头
$paths | map(join(".")) as $headers |
# 生成数据行
[$paths[] as $p | getpath($p)] as $row |
$headers, $row | @csv
'

执行后输出：

"creation_date","data.transactionType","data.location.zoneId","data.media.typeCode","data.media.postPaymentEvent.0.transactionDate","id"
"2023/06/16 15:55:09","val",2,15,"2022-12-26T16:50:00+01:00","000-0"

方案2：展开数组所有元素（多条记录） #

如果需要把postPaymentEvent数组的每个元素都转为单独的CSV行，同时保留其他固定字段的值，可以用以下命令：

jq --raw-output '
# 定义固定字段的路径
[
  ["creation_date"],
  ["data", "transactionType"],
  ["data", "location", "zoneId"],
  ["data", "media", "typeCode"],
  ["id"]
] as $fixed_paths |
# 生成固定字段的表头
$fixed_paths | map(join(".")) as $fixed_headers |
# 数组元素的表头
["transactionDate", "vehicleType", "vehicleId"] as $array_headers |
# 合并表头
$fixed_headers + $array_headers,
# 遍历数组，生成每行数据
.data.media.postPaymentEvent[] |
(
  $fixed_paths[] as $p | getpath($p)
) + [.transactionDate, .vehicleType, .vehicleId] |
@csv
'

执行后输出：

"creation_date","data.transactionType","data.location.zoneId","data.media.typeCode","id","transactionDate","vehicleType","vehicleId"
"2023/06/16 15:55:09","val",2,15,"000-0","2022-12-26T16:50:00+01:00",1,1533
"2023/06/16 15:55:09","val",2,15,"000-0","2022-12-27T16:31:00+01:00",1,1533
"2023/06/16 15:55:09","val",2,15,"000-0","2022-12-28T15:50:00+01:00",1,1533

内容的提问来源于stack exchange，提问作者user4292809