具有多个 group by 的 Mongodb 聚合

Question

我有一个 mongodb 集合测试。数据看起来像这样 -

{ 
    "_id" : ObjectId("5dd271d30c90441941eb92b1"), 
    "h_id" : NumberInt(1), 
    "ota" : NumberInt(1), 
    "search_date" : ISODate("2019-09-09T00:00:00.000+0000"), 
    "data" : [{
        "price" : NumberInt(12500), 
        "code" : "02246933", 
    }
    {
        "price" : NumberInt(11500), 
        "code" : "02246934", 
    }
    {
        "price" : NumberInt(13500), 
        "code" : "02246935", 
    }
    {
        "price" : NumberInt(14500), 
        "code" : "02246936", 
    }]
}
{ 
    "_id" : ObjectId("5dd271d30c90441941eb92b2"), 
    "h_id" : NumberInt(1), 
    "ota" : NumberInt(1), 
    "search_date" : ISODate("2019-09-10T00:00:00.000+0000"), 
    "data" : [{
        "price" : NumberInt(2500), 
        "code" : "102246933", 
    }
    {
        "price" : NumberInt(1500), 
        "code" : "102246934", 
    }
    {
        "price" : NumberInt(3500), 
        "code" : "102246935", 
    }
    {
        "price" : NumberInt(4500), 
        "code" : "102246936", 
    }]
}

我想编写一个查询，它可以给出类似的结果 -

{ 
    "h_id" : NumberInt(1), 
    "ota_group": [{
    "ota": NumberInt(1),
    "search_date_group": [{
        "search_date": ISODate("2019-09-09T00:00:00.000+0000"),
        "min_price": NumberInt(11500),
        "min_code": "02246934",
        "max_price": NumberInt(14500),
        "max_code": "02246936"
        },
        {
        "search_date": ISODate("2019-09-10T00:00:00.000+0000"),
        "min_price": NumberInt(1500),
        "min_code": "102246934",
        "max_price": NumberInt(4500),
        "max_code": "102246936"
        }]
    }]
}
{ 
    "h_id" : NumberInt(2), 
    "ota_group": [{
    "ota": NumberInt(1),
    "search_date_group": [{
        "search_date": ISODate("2019-09-09T00:00:00.000+0000"),
        "min_price": NumberInt(11500),
        "min_code": "02246934",
        "max_price": NumberInt(14500),
        "max_code": "02246936"
        },
        {
        "search_date": ISODate("2019-09-10T00:00:00.000+0000"),
        "min_price": NumberInt(1500),
        "min_code": "102246934",
        "max_price": NumberInt(4500),
        "max_code": "102246936"
        }]
    }]
}

目标:我想使用 h_id 对文档进行分组，然后应使用 ota 再次对每个子组进行分组。此外，这些子组应使用 search_date 进行分组。对于每个 search_date，我需要 max_price、max_code 和 min_price、min_code。根据我的理解，我需要执行多个(嵌套)分组来实现此结果。

我是 Mongodb 的新用户，并尝试编写查询 -

db.test.aggregate(
[  
    {
        "$match": {
          'h_id': 1
    },
    { "$unwind": "$data" },
    { "$group": 
        {
            '_id': '$_id',
            'h_id': { $first: "$h_id" },
            'ota': { $first: "$ota" },
            'search_date': { $first: "$search_date" },
            'min_price': {
                '$min': "$data.price",
            },
            'min_price': {
                '$min': "$data.price"
            },
            'max_price': {
                '$max': "$data.price"
            },
            'max_price': {
                '$max': "$data.price"
            }
        }
    },
     { "$group": 
        {
            '_id': {h_id: "$h_id"},            
            'info': { $push:  { 'search_date': "$search_date", 'ota': "$ota", 'min_price': "$min_price", 'max_price': "$max_price", 'min_price': "$min_price", 'max_price': "$max_price" } }
        }
    },
    { "$group": 
        {
            '_id': "$_id.h_id",            
            'info': { $first: "$info" },
        }
    },
]);  

但还没有接近要求。任何人都可以给出一些提示/想法如何实现这一目标..

Answer 1

有趣的问题，幸运的是(至少)有一个答案。

技巧是双重的:

• 使用 $sort 阶段，后接 $first 和 $last 累加器，而不是 $min 和 $max。
• 从更精确的小组赛阶段开始，然后“取消分组”。

这是您需要的查询:

db.collection.aggregate([
  {
    $unwind: "$data"
  },
  {
    $sort: {
      "data.price": 1
    }
  },
  {
    $group: {
      _id: {
        h_id: "$h_id",
        ota: "$ota",
        search_date: "$search_date"
      },
      "min_price": {
        $first: "$data.price"
      },
      "min_code": {
        $first: "$data.code"
      },
      "max_price": {
        $last: "$data.price"
      },
      "max_code": {
        $last: "$data.code"
      },

    }
  },
  {
    $group: {
      _id: {
        h_id: "$_id.h_id",
        ota: "$_id.ota",

      },
      search_date_group: {
        $push: {
          "search_date": "$_id.search_date",
          "max_code": "$max_code",
          "max_price": "$max_price",
          "min_code": "$min_code",
          "min_price": "$min_price",

        }
      }
    }
  },
  {
    $group: {
      _id: {
        h_id: "$_id.h_id",

      },
      "ota_group": {
        $push: {
          ota: "$_id.ota",
          search_date_group: "$search_date_group"
        }
      }
    }
  }
])

它将准确输出您需要的内容:

 {
    "_id": {
      "h_id": 1
    },
    "ota_group": [
      {
        "ota": 1,
        "search_date_group": [
          {
            "max_code": "02246936",
            "max_price": 14500,
            "min_code": "02246934",
            "min_price": 11500,
            "search_date": ISODate("2019-09-09T00:00:00Z")
          },
          {
            "max_code": "102246936",
            "max_price": 4500,
            "min_code": "102246934",
            "min_price": 1500,
            "search_date": ISODate("2019-09-10T00:00:00Z")
          }
        ]
      },
      {
        "ota": 2,
        "search_date_group": [
          {
            "max_code": "102246936",
            "max_price": 4500,
            "min_code": "102246934",
            "min_price": 1500,
            "search_date": ISODate("2019-09-10T00:00:00Z")
          },
          {
            "max_code": "02246936",
            "max_price": 14500,
            "min_code": "02246934",
            "min_price": 11500,
            "search_date": ISODate("2019-09-09T00:00:00Z")
          }
        ]
      }
    ]
  }

你可以测试一下here .