具有 2 个集合的 Mongodb 聚合 [英] Mongodb aggregation with 2 collections

问题描述

在 mongodb 中，我有 2 个这样的集合

In mongodb I have 2 collections like this

var collection1Schema = new Schema({
    moneyPaid:{
        type:Number
    }
}, {collection: 'collection1'});

var collection2 = new Schema({
    coll_id: {
        type: Schema.ObjectId,
        ref: 'collection1'
    },
    isBook: {
       type: Boolean,
    }
}, {collection: 'collection2'});

我想要 collection1 中所有 moneypaid 的总和，它在 collection2 中有 isBook 真值.

I want the sum of all moneypaid from collection1 which has isBook true value in collection2.

推荐答案

根据您的系统需求，我认为可以通过创建一个合并 collection1collection1 和 collection2.举个例子:

Depending on what your system needs are, I think the model design could be simplified by creating just one collection that merges all the attributes in collection1 and collection2. As an example:

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

var accountSchema = new Schema({
    moneyPaid:{
        type: Number
    },
    isBook: {
       type: Boolean,
    }
}, {collection: 'account'});

var Account = mongoose.model('Account', accountSchema);

然后您可以在其中运行聚合管道

in which you can then run the aggregation pipeline

var pipeline = [
    { 
        "$match": { "isBook" : true }
    },
    { 
        "$group": {
            "_id": null,
            "total": { "$sum": "$moneyPaid"}
        }
    }
];

Account.aggregate(pipeline, function(err, results) {
    if (err) throw err;
    console.log(JSON.stringify(results, undefined, 4));
});

但是，使用当前的架构设计，您必须首先获取在 collection2 中具有 isBook 真值的 collection1 的 id，然后使用该 id 列表作为 $match 在 collection1 模型聚合中查询，类似如下:

However, with the current schema design you would have to first get the ids for collection1 which have isBook true value in collection2 and then use that id list as the $match query in the collection1 model aggregation, something like the following:

collection2Model.find({"isBook": true}).lean().exec(function (err, objs){
    var ids = objs.map(function (o) { return o.coll_id; }),
        pipeline = [
            { 
                "$match": { "_id" : { "$in": ids } }
            },
            { 
                "$group": {
                    "_id": null,
                    "total": { "$sum": "$moneyPaid"}
                }
            }
        ];

    collection1Model.aggregate(pipeline, function(err, results) {
        if (err) throw err;
        console.log(JSON.stringify(results, undefined, 4));
    });
});

这篇关于具有 2 个集合的 Mongodb 聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！