Mongo聚合查询

Question

我有一个有趣的问题。例如，我有一个包含start_date和end_date的mongo集合。我需要创建一个聚合查询（或mapReduce），它将返回一个日期重叠的数组数组。

例如：

集合：

[{
  _id: 1111111,
  start_date: '10/04/2016',
  end_data: '13/04/2016'
},
{
  _id: 2222222,
  start_date: '11/04/2016',
  end_data: '20/04/2016'
},
{
  _id: 3333333,
  start_date: '10/05/2016',
  end_data: '13/05/2016'
},
{
  _id: 44444444,
  start_date: '12/05/2016',
  end_data: '15/05/2016'
},
{
  _id: 55555555,
  start_date: '18/04/2016',
  end_data: '22/04/2016'
}]

应返回

[[1111111, 2222222], [3333333,44444444], [2222222, 55555555]]

Answer 1

我认为这样做就可以了，必须尝试使用真实数据进行验证。

我把它作为一个总体想法发布，如果你有更清晰的东西，请任何人验证并修正答案。

db.collection.aggregate([
{$project: {
        _id:$_id,
        dates: [{no:1, date:$start_date},{no:2,date:$end_date}]
}},
{$unwind: $dates},
{$group: _id: $dates.date ,
        start_dates: {$addToSet: {$cond:{if:{$eq:[1, $dates.no]}, 
            then: [$_id], 
            else: null}}},
        end_dates: {$addToSet: {$cond:{if:{$eq:[2, $dates.no]}, 
            then: $_id, 
            else: null}}}
},
{$match: {$start_dates.1:{$exists:true}, 
        $end_dates.1{$exists:true}}}
]), //At this point you have objects like { aDate, [lst of ids with that date as start], [lst of ids with that date as end]}
{$unwind: $start_dates},
{$group: {_id: {date:$_id, id:$start_dates.1}, // You still have 1 array of array per dates, you can always put "_id: 1" to have only 1 array as answer
        res: {$addToSet:{$start_dates: {$each: $end_dates}}}}
}