Get first element in array and return using Aggregate?
Since 3.2, we can use $arrayElemAt to get the first element in an array db.my_collection.aggregate([ { $project: { resp : { $arrayElemAt: [‘$my_field’,0] } }} ])
aggregation-framework
Mongoose sort the aggregated result
The results returned from the aggregation pipeline are just plain objects. So you do the sorting as a pipeline stage, not as a separate operation: Recommend.aggregate( [ // Grouping pipeline { “$group”: { “_id”: ‘$roomId’, “recommendCount”: { “$sum”: 1 } }}, // Sorting pipeline { “$sort”: { “recommendCount”: -1 } }, // Optionally limit results … Read more
MongoDB Regex Search on Integer Value
If you are wanting to do a pattern match on numbers, the way to do it in mongo is use the $where expression and pass in a pattern match. > db.test.find({ $where: “/^123.*/.test(this.example)” }) { “_id” : ObjectId(“4bfc3187fec861325f34b132”), “example” : 1234 }
Mongodb aggregation pipeline how to limit a group push
Suppose the bottom left coordinates and the upper right coordinates are respectively [0, 0] and [100, 100]. From MongoDB 3.2 you can use the $slice operator to return a subset of an array which is what you want. db.collection.aggregate([ { “$match”: { “loc”: { “$geoWithin”: { “$box”: [ [0, 0], [100, 100] ] } }} … Read more
If Mongo $lookup is a left outer join, then how come it excludes non-matching documents?
This behavior isn’t related to $lookup, it’s because the default behavior for $unwind is to omit documents where the referenced field is missing or an empty array. To preserve the unwound documents even when profile.universities is an empty array, you can set its preserveNullAndEmptyArrays option to true: db.users.aggregate([ { $unwind: “$profile”, $unwind: { path: “$profile.universities”, … Read more
Calculate the median in MongoDB aggregation framework
The median is somewhat tricky to compute in the general case, because it involves sorting the whole data set, or using a recursion with a depth that is also proportional to the data set size. That’s maybe the reason why many databases don’t have a median operator out of the box (MySQL doesn’t have one, … Read more
Mongodb concat int and string
Add Stage 2.5 😛 { $project:{ FileName:1, FileSize:{$substr:[“$FileSize”, 0, -1 ]} } } FileSize is a integer and there is no operation to convert it to String. So you can use hack, and use substr to convert it to string, 0 for starting and -1 rest of the string.
MongoDB to Use Sharding with $lookup Aggregation Operator
As the docs you quote indicate, you can’t use $lookup on a sharded collection. So the best practice workaround is to perform the lookup yourself in a separate query. Perform your aggregate query. Pull the “localField” values from your query results into an array, possibly using Array#map. Perform a find query against the “from” collection, … Read more
Group and count with condition
What you need is the $cond operator of aggregation framework. One way to get what you want would be: db.foo.aggregate([ { $project: { item: 1, lessThan10: { // Set to 1 if value < 10 $cond: [ { $lt: [“$value”, 10 ] }, 1, 0] }, moreThan10: { // Set to 1 if value > … Read more
How to aggregate by date when a full timestamp is given in aggregation framework?
You can do this by using the following aggregation operators: $group $year $month $dayOfMonth This gives you the error count for each date: db.errors.aggregate( { $group : { _id: { year : { $year : “$date” }, month : { $month : “$date” }, day : { $dayOfMonth : “$date” }, }, count: { $sum: … Read more