Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

node.js - Mongoose Query to filter an array and Populate related content

I'm trying to query the property that is an array of both reference to another schema and some additional data. For better clarification, here's the schema:

    var orderSchema = new Schema({
        orderDate: Date,
        articles: [{
            article: {
                type: Schema.Types.ObjectId,
                ref: 'Article'
            },
            quantity: 'Number'
        }]
    }),
    Order = mongoose.model('Order', orderSchema);

While I managed to successfully query the reference, i.e.:

Order.find({}).populate('articles.article', null, {
    price: {
        $lte: 500
    }
}).exec(function(err, data) {
    for (var order of data) {
        for (var article of order.articles) {
            console.log(article);
        }
    }
});

I have some issues querying the quantity attribute, i.e. this doesn't work:

Order.find({}).where({
    'articles.quantity': {
        $gte: 5
    }
}).populate('articles.article', null, {
    /*price: {
        $lte: 500
    }*/
}).exec(function(err, data) {
    for (var order of data) {
        for (var article of order.articles) {
            console.log(article);
        }
    }
});

Is it even possible to base the query on quantity? And if so, what would be the best approach?

Thank you!

UPDATE:

The problem is, the result is either a complete array, or nothing (see updated question). I want to get only those records that have quantity more or the same as 5. With your (and mine) approach I get either no records at all (if I set $gte: 5001) or both records (if I set $gte:5000)

{
    "_id": ObjectId('56fe76c12f7174ac5018054f'),
    "orderDate": ISODate('2016-04-01T13:25:21.055Z'),
    "articles": [
        {
            "article": ObjectId('56fe76c12f7174ac5018054b'),
            "quantity": 5000,
            "_id": ObjectId('56fe76c12f7174ac50180551')
        },
        {
            "article": ObjectId('56fe76c12f7174ac5018054c'),
            "quantity": 1,
            "_id": ObjectId('56fe76c12f7174ac50180552')
        }
    ],
    "__v": 1
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You need to "project" the match here since all the MongoDB query does is look for a "document" that has "at least one element" that is "greater than" the condition you asked for.

So filtering an "array" is not the same as the "query" condition you have.

A simple "projection" will just return the "first" matched item to that condtion. So it's probably not what you want, but as an example:

Order.find({ "articles.quantity": { "$gte": 5 } })
    .select({ "articles.$": 1 })
    .populate({
        "path": "articles.article",
        "match": { "price": { "$lte": 500 } }
    }).exec(function(err,orders) {
       // populated and filtered twice
    }
)

That "sort of" does what you want, but the problem is really going to be that will only ever return at most one element within the "articles" array.

To do this properly you need .aggregate() to filter the array content. Ideally this is done with MongoDB 3.2 and $filter. But there is also a special way to .populate() here:

Order.aggregate(
    [
        { "$match": { "artciles.quantity": { "$gte": 5 } } },
        { "$project": {
            "orderdate": 1,
            "articles": {
                "$filter": {
                    "input": "$articles",
                    "as": "article",
                    "cond": {
                       "$gte": [ "$$article.quantity", 5 ]
                    }
                }
            },
            "__v": 1
        }}
    ],
    function(err,orders) {
        Order.populate(
            orders.map(function(order) { return new Order(order) }),
            {
                "path": "articles.article",
                "match": { "price": { "$lte": 500 } }
            },
            function(err,orders) {
                // now it's all populated and mongoose documents
            }
        )
    }
)

So what happens here is the actual "filtering" of the array happens within the .aggregate() statement, but of course the result from this is no longer a "mongoose document" because one aspect of .aggregate() is that it can "alter" the document structure, and for this reason mongoose "presumes" that is the case and just returns a "plain object".

That's not really a problem, since when you see the $project stage, we are actually asking for all of the same fields present in the document according to the defined schema. So even though it's just a "plain object" there is no problem "casting" it back into an mongoose document.

This is where the .map() comes in, as it returns an array of converted "documents", which is then important for the next stage.

Now you call Model.populate() which can then run the further "population" on the "array of mongoose documents".

The result then is finally what you want.


MongoDB older versions than 3.2.x

The only things that really change here are the aggregation pipeline, So that is all that needs to be included for brevity.

MongoDB 2.6 - Can filter arrays with a combination of $map and $setDifference. The result is a "set" but that is not a problem when mongoose creates an _id field on all sub-document arrays by default:

    [
        { "$match": { "artciles.quantity": { "$gte": 5 } } },
        { "$project": {
            "orderdate": 1,
            "articles": {
                "$setDiffernce": [
                   { "$map": {
                      "input": "$articles",
                      "as": "article",
                      "in": {
                         "$cond": [
                             { "$gte": [ "$$article.price", 5 ] },
                             "$$article",
                             false
                         ]
                      }
                   }},
                   [false]
                ]
            },
            "__v": 1
        }}
    ],

Older revisions of than that must use $unwind:

    [
        { "$match": { "artciles.quantity": { "$gte": 5 } }},
        { "$unwind": "$articles" },
        { "$match": { "artciles.quantity": { "$gte": 5 } }},
        { "$group": {
          "_id": "$_id",
          "orderdate": { "$first": "$orderdate" },
          "articles": { "$push": "$articles" },
          "__v": { "$first": "$__v" }
        }}
    ],

The $lookup Alternative

Another alternate is to just do everything on the "server" instead. This is an option with $lookup of MongoDB 3.2 and greater:

Order.aggregate(
    [
        { "$match": { "artciles.quantity": { "$gte": 5 } }},
        { "$project": {
            "orderdate": 1,
            "articles": {
                "$filter": {
                    "input": "$articles",
                    "as": "article",
                    "cond": {
                       "$gte": [ "$$article.quantity", 5 ]
                    }
                }
            },
            "__v": 1
        }},
        { "$unwind": "$articles" },
        { "$lookup": {
            "from": "articles",
            "localField": "articles.article",
            "foreignField": "_id",
            "as": "articles.article"
        }},
        { "$unwind": "$articles.article" },
        { "$group": {
          "_id": "$_id",
          "orderdate": { "$first": "$orderdate" },
          "articles": { "$push": "$articles" },
          "__v": { "$first": "$__v" }
        }},
        { "$project": {
            "orderdate": 1,
            "articles": {
                "$filter": {
                    "input": "$articles",
                    "as": "article",
                    "cond": {
                       "$lte": [ "$$article.article.price", 500 ]
                    }
                }
            },
            "__v": 1
        }}
    ],
    function(err,orders) {

    }
)

And though those are just plain documents, it's just the same results as what you would have got from the .populate() approach. And of course you can always go and "cast" to mongoose documents in all cases again if you really must.

The "shortest" Path

This really goes back to the orginal statement where you basically just "accept" that the "query" is not meant to "filter" the array content. The .populate() can happilly do so becuse it's just another "query" and is stuffing in "documents" by convenience.

So if you really are not saving "bucketloads" of bandwith by the removal of additional array members in the orginal document array, then just .filter() them out in post processing code:

Order.find({ "articles.quantity": { "$gte": 5 } })
    .populate({
        "path": "articles.article",
        "match": { "price": { "$lte": 500 } }
    }).exec(function(err,orders) {
        orders = orders.filter(function(order) {
            order.articles = order.articles.filter(function(article) {
                return (
                    ( article.quantity >= 5 ) &&
                    ( article.article != null )
                )
            });
            return order.aricles.length > 0;
        })

        // orders has non matching entries removed            
    }
)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...