使用MongoDB聚合在同一文档中查找两个集合的集合交集 [英] Use MongoDB aggregation to find set intersection of two sets within the same document

问题描述

我正在尝试使用Mongo聚合框架来查找同一文档中哪里存在具有不同唯一集的记录.一个例子可以最好地解释这一点:

I'm trying to use the Mongo aggregation framework to find where there are records that have different unique sets within the same document. An example will best explain this:

这里的文档不是我的真实数据，但在概念上是相同的:

Here is a document that is not my real data, but conceptually the same:

db.house.insert(
{
    houseId : 123,
    rooms: [{ name : 'bedroom',
          owns : [
            {name : 'bed'},
            {name : 'cabinet'}
        ]},
        { name : 'kitchen',
          owns : [
            {name : 'sink'},
            {name : 'cabinet'}
        ]}],
       uses : [{name : 'sink'},
               {name : 'cabinet'},
               {name : 'bed'},
               {name : 'sofa'}]
}
)

请注意，存在两个具有相似项目的层次结构.也可以使用非拥有的物品.我想找到这样的文档:有一所房子使用了它不拥有的东西.

Notice that there are two hierarchies with similar items. It is also possible to use items that are not owned. I want to find documents like this one: where there is a house that uses something that it doesn't own.

到目前为止，我已经使用如下所示的聚合框架构建了结构.这使我得到2套不同的物品.但是，我还找不到任何可以给我设定相交结果的东西.请注意，由于以下原因，无法简单地计算集合大小:['couch'，'cabinet']与['sofa'，'cabinet']比较.

So far I've built up the structure using the aggregate framework like below. This gets me to 2 sets of distinct items. However I haven't been able to find anything that could give me the result of a set intersection. Note that a simple count of set size will not work due to something like this: ['couch', 'cabinet'] compare to ['sofa', 'cabinet'].

{'$unwind':'$uses'}
{'$unwind':'$rooms'}
{'$unwind':'$rooms.owns'}
{'$group' : {_id:'$houseId', 
             use:{'$addToSet':'$uses.name'}, 
             own:{'$addToSet':'$rooms.owns.name'}}}

产生:

{ _id : 123,
  use : ['sink', 'cabinet', 'bed', 'sofa'],
  own : ['bed', 'cabinet', 'sink']
}

然后如何在管道的下一阶段中找到使用权和拥有权的设定交集?

How do I then find the set intersection of use and own in the next stage of the pipeline?

推荐答案

仅适用于MongoDB 2.6 +

从MongoDB 2.6开始，在项目管道阶段有可用的设置操作.使用新操作来解决此问题的方法是:

As of MongoDB 2.6, there are set operations available in the project pipeline stage. The way to answer this problem with the new operations is:

db.house.aggregate([
    {'$unwind':'$uses'},
    {'$unwind':'$rooms'},
    {'$unwind':'$rooms.owns'},
    {'$group' : {_id:'$houseId', 
             use:{'$addToSet':'$uses.name'}, 
             own:{'$addToSet':'$rooms.owns.name'}}},
    {'$project': {int:{$setIntersection:["$use","$own"]}}}
]);

这篇关于使用MongoDB聚合在同一文档中查找两个集合的集合交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！