The question here is actually about something different and does not need $lookup at all. But for anyone arriving here purely from the title of “filtering after $lookup” then these are the techniques for you: MongoDB 3.6 – Sub-pipeline db.test.aggregate([ { “$match”: { “id”: 100 } }, { “$lookup”: { “from”: “test”, “let”: { “id”: … Read more
It is somewhat confusing since the names are similar, but the group() command is a different feature and implementation from the $group pipeline operator in the Aggregation Framework. The group() command, Aggregation Framework, and MapReduce are collectively aggregation features of MongoDB. There is some overlap in features, but I’ll attempt to explain the differences and … Read more
To calculate totals and return a subset, you need to apply grouping and skip/limit to the same dataset. For that you can utilise facets For example to show 3rd page, 10 documents per page: db.Order.aggregate([ { ‘$match’ : { “company_id” : ObjectId(“54c0…”) } }, { ‘$sort’ : { ‘order_number’ : -1 } }, { ‘$facet’ … Read more
By default aggregation in MongoDB occurs in memory and pipeline stages have limit of 100 Mb RAM. Looks like you have exceeded this threshold. To handle large dataset you should enable aggregation pipeline stages to write data to temporary files. Use allowDiskUse option for that: db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.aggregate([ { “$project” : { “visitor_localdate” : 1 , “_id” … Read more
MongoDB is complaining because you have an unrecognised pipeline stage specification “count”: { “$sum”: 1 } in your pipeline. Your original pipeline when formatted properly db.hashtag.aggregate([ { “$group”: { “_id”: { “year”: { “$year”: “$tweettime” }, “dayOfYear”: { “$dayOfYear”: “$tweettime” }, “interval”: { “$subtract”: [ { “$minute”: “$tweettime” }, { “$mod”: [{ “$minute”: “$tweettime”}, 15] … Read more
$group does not use index data. From the mongoDB docs: The $match and $sort pipeline operators can take advantage of an index when they occur at the beginning of the pipeline. The $geoNear pipeline operator takes advantage of a geospatial index. When using $geoNear, the $geoNear pipeline operation must appear as the first stage in … Read more
With modern releases ( since MongoDB 3.4 ) you would use $switch, which is basically the counterpart to switch or case keywords in other language implementations: db.items.aggregate([ { “$project”: { “name”: 1, “customfield”: { “$switch”: { “branches”: [ { “case”: { “$eq”: [ “$field1”, “4” ] }, “then”: 30 }, { “case”: { “$eq”: [ … Read more
You are beginning to think along the right lines here as you were headed in the right direction. Changing your SQL mindset, “distinct” is really just another way of writing a $group operation in either language. That means you have two group operations happening here and, in aggregation pipeline terms, two pipeline stages. Just with … Read more
2017 Update Such a well put question deserves a modern response. The sort of array filtering requested can actually be done in modern MongoDB releases post 3.2 via simply $match and $project pipeline stages, much like the original plain query operation intends. db.accounts.aggregate([ { “$match”: { “email” : “john.doe@acme.com”, “groups”: { “$elemMatch”: { “name”: “group1”, … Read more
Use $slice. db.foo.find({ bar : “xyz” } , { my_array : { $slice : [n , 1] } } ) will retrieve the nth element of the array “my_array” of all documents in the foo collection where bar = “xyz”. Some other examples from the MongoDB documentation: db.posts.find({}, {comments:{$slice: 5}}) // first 5 comments db.posts.find({}, … Read more