如何用JQ过滤指定键并将值合并到单个CSV单元格

给定JSON数据 #

{"data": [
  {
    "account_id": "123",
    "account_status": "active",
    "name": "john doe",
    "email": "johndoe@anywhere.com",
    "product_access": []
  },
  {
    "account_id": "345",
    "account_status": "active",
    "name": "jane doe",
    "email": "janedoe@anywhere.com",
    "last_active": "2023-08-23T13:03:27.811473590Z",
    "product_access": [
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme1.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme2.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme3.com"
      },
      {
        "name": "Product 2",
        "key": "product2",
        "url": "acme4.com",
        "last_active": "2023-08-23T13:03:27.811473590Z"
      },
      {
        "name": "Product 3",
        "key": "product3",
        "url": "acme5.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme4.com",
        "last_active": "2023-08-17T18:21:52.472085713Z"
      }
    ]
  }
]
}

需求输出CSV格式 #

"account_id", "account_status", "name", "email", "Product 1", "Product 2", "Product 3"
"123", "active", "john doe", "johndoe@anywhere.com", ",,"
"345", "active", "jane doe", "janedoe@anywhere.com", "acme1.com, acme2.com, acme3.com, acme4.com", "acme4.com", "acme5.com"

尝试的JQ代码（存在问题） #

jq -r '["account_id", "account_status", "name", "email", "product 1", "product 2", "product 3"],
        (.data[] | [.account_id, .account_status, .name, .email,
                    (.product_access[] | select(.key == "product1").url // ""),
                    (.product_access[] | select(.key == "product2").url // ""),
                    (.product_access[] | select(.key == "product3").url // "")] | join(",")) | @csv'

问题：无法将同一产品的多个URL合并到单个CSV单元格中，会导致单元格被错误拆分。

正确的JQ解决方案 #

jq -r '
# 定义CSV表头
["account_id", "account_status", "name", "email", "Product 1", "Product 2", "Product 3"],
# 处理每个账户条目
(.data[] | 
  # 将product_access按key分组，提取每个组的URL列表
  (.product_access | group_by(.key) | map({key: .[0].key, urls: [.[].url]})) | 
  # 初始化基础字段，并用reduce将URL列表映射到对应产品字段
  reduce .[] as $item (
    {account_id: .account_id, account_status: .account_status, name: .name, email: .email, "Product 1": "", "Product 2": "", "Product 3": ""};
    .["Product " + ($item.key | ltrimstr("product"))] = ($item.urls | join(", "))
  ) | 
  # 严格按表头顺序提取值生成数组
  [.account_id, .account_status, .name, .email, 
   ."Product 1" // ",,", 
   ."Product 2" // ",,", 
   ."Product 3" // ",,"]
) | 
# 转换为标准CSV格式
@csv'

关键逻辑说明 #

1. 分组产品记录：用group_by(.key)把同一产品的访问记录归为一组，再提取每组的所有URL形成列表。
2. 映射产品字段：通过reduce遍历分组结果，将每个产品的URL列表用, 连接成单个字符串，赋值到对应的表头字段。
3. 空值适配：用// ",,"将空字符串替换为需求中的,,，如果遵循标准CSV规范，可去掉此部分，空单元格会自动输出为""。

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