如何在jq中实现SQL风格的GROUP BY聚合函数（COUNT、SUM等）

我刚好最近在用jq处理类似的聚合需求，结合你提到的主人-宠物模型，我来一步步演示如何用jq模拟SQL里的常见聚合函数——包括COUNT、SUM、MAX、MIN、AVG，还有PostgreSQL的ARRAY_AGG。先给大家看一下我们要用的源数据：

[
  {
    "owner": "Alice",
    "pets": [
      {"name": "Mittens", "age": 3},
      {"name": "Whiskers", "age": 1}
    ]
  },
  {
    "owner": "Bob",
    "pets": [
      {"name": "Rex", "age": 5},
      {"name": "Fido", "age": 2},
      {"name": "Spot", "age": 4}
    ]
  },
  {
    "owner": "Charlie",
    "pets": [
      {"name": "Purr", "age": 2}
    ]
  }
]

1. 模拟SQL COUNT：统计每位主人的宠物数量 #

对应SQL逻辑：SELECT owner, COUNT(pet) FROM owners GROUP BY owner，jq里用length统计数组长度即可：

.[] | {owner: .owner, pet_count: .pets | length}

预期输出：

{"owner":"Alice","pet_count":2}
{"owner":"Bob","pet_count":3}
{"owner":"Charlie","pet_count":1}

2. 模拟SQL SUM：计算每位主人的宠物年龄总和 #

对应SQL逻辑：SELECT owner, SUM(pet.age) FROM owners GROUP BY owner，用map(.age)提取年龄数组后，add求和：

.[] | {owner: .owner, total_pet_age: .pets | map(.age) | add}

预期输出：

{"owner":"Alice","total_pet_age":4}
{"owner":"Bob","total_pet_age":11}
{"owner":"Charlie","total_pet_age":2}

3. 模拟SQL MAX/MIN：找出每位主人宠物的最大/最小年龄 #

MAX（最大年龄） #

对应SQL逻辑：SELECT owner, MAX(pet.age) FROM owners GROUP BY owner，用map(.age) | max实现：

.[] | {owner: .owner, max_pet_age: .pets | map(.age) | max}

预期输出：

{"owner":"Alice","max_pet_age":3}
{"owner":"Bob","max_pet_age":5}
{"owner":"Charlie","max_pet_age":2}

MIN（最小年龄） #

对应SQL逻辑：SELECT owner, MIN(pet.age) FROM owners GROUP BY owner，用map(.age) | min实现：

.[] | {owner: .owner, min_pet_age: .pets | map(.age) | min}

预期输出：

{"owner":"Alice","min_pet_age":1}
{"owner":"Bob","min_pet_age":2}
{"owner":"Charlie","min_pet_age":2}

4. 模拟SQL AVG：计算每位主人宠物的平均年龄 #

对应SQL逻辑：SELECT owner, AVG(pet.age) FROM owners GROUP BY owner，用总和除以数量即可，想要格式化小数可以用round(n)：

.[] | {
  owner: .owner,
  avg_pet_age: ((.pets | map(.age) | add) / (.pets | length)) | round(2)
}

预期输出：

{"owner":"Alice","avg_pet_age":2}
{"owner":"Bob","avg_pet_age":3.67}
{"owner":"Charlie","avg_pet_age":2}

5. 模拟PostgreSQL ARRAY_AGG：聚合宠物名称为数组 #

对应SQL逻辑：SELECT owner, ARRAY_AGG(pet.name) FROM owners GROUP BY owner，直接用map(.name)提取名称数组：

.[] | {owner: .owner, pet_names: .pets | map(.name)}

预期输出：

{"owner":"Alice","pet_names":["Mittens","Whiskers"]}
{"owner":"Bob","pet_names":["Rex","Fido","Spot"]}
{"owner":"Charlie","pet_names":["Purr"]}

组合多个聚合函数 #

如果想一次性获取所有聚合结果，把字段合并到一个对象里即可：

.[] | {
  owner: .owner,
  pet_count: .pets | length,
  total_pet_age: .pets | map(.age) | add,
  max_pet_age: .pets | map(.age) | max,
  min_pet_age: .pets | map(.age) | min,
  avg_pet_age: ((.pets | map(.age) | add) / (.pets | length)) | round(2),
  pet_names: .pets | map(.name)
}

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