Aggregation using Project and Slice, mongodb

Question

I've seen similar questions but far to complex for a newbie like myself. Plus none of the answers offered a syntax break down.

My database:

    name :"Kim"
    points: [ 
       { category:"Purchase",
         points: -50},
       { category: "Wage",
         points : 275},
       { category: "Purchase",
         points: -40}
    ]

    name :"Meghan"
    points: [ 
       { category:"Contest",
         points: 130},
       { category: "Purchase",
         points : -25},
       { category: "Games",
         points : 50}
     ]
    ]

So in the mongo shell I'm trying to get it find every points.category that equals "Purchase" but only return to me the last one. I attempted to achieve this by using aggregate, $project and $slice. My issue is that I don't understand the syntax well enough to know whether $slice goes inside $project or outside separated by a comma. I understand the syntax when I use $project by itself or $slice by itself but I have no clue how to use all these things together to something magical. If someone could help me solve my problem and explain how to combine all these things properly together, I'd be forever indebted. I've read over the docs but nothing showed me how to use everything all at once.

I want to search the student who's name is Kim points to find the last Purchase she made. The result I want is [{points:-40}]

The part you are missing is you need to [`$filter`](https://docs.mongodb.com/manual/reference/operator/aggregation/filter/) the array content for `"Purchase"` first **before** you apply `$slice` or even [`$arrayElemAt`](https://docs.mongodb.com/manual/reference/operator/aggregation/arrayElemAt/) in order to get the **last** element. So `$filter` returns the array with just those elements. Wrapping that statement with `$slice` and `-1` for the index returns the "last" from that "filtered" array. — Neil Lunn, Oct 16 '17 at 03:58
See [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/q/3985214/2313887) for example usage of `$filter` in addition to the linked documentation. — Neil Lunn, Oct 16 '17 at 04:03
Okay i came up with this: ` db.students.aggregate([ { $project: { points: { $filter : { input: "$points", as:"pts", cond: {$eq: ["$$pts.category", "Purchase"]}}}}}]).pretty()` The problem is that it returns every student's purchases. How do I make it return just kims and where do I put the slice to get only the last purchase. — Ari Bryant, Oct 16 '17 at 04:26

score 0 · Answer 1 · answered Oct 16 '17 at 06:04

0

If you can use more projection with other aggregation operator, you may try this.

db.students.aggregate([
{ $match : { name : "Kim" } },
{ $project: 
    { 
        points: {
            $filter : { input: "$points", as:"pts", cond: {$eq: ["$$pts.category", "Purchase"]} }
            }
    }
},
{ $project: 
    { 
        _id : 0,
        points: { $arrayElemAt: [{$slice: [ "$points.points", -1]},0] }
    }
}
])

answered Oct 16 '17 at 06:04

Hydro Choy

36
4

Thanks Hydro, I only used the top part of your code, not the second projection and it worked flawlessly. Thanks everyone for all the help – Ari Bryant Oct 17 '17 at 02:52
The first projection will give a result of "all Purchase points from Kim", the second projection will give a result of "the last Purchase points from Kim". – Hydro Choy Oct 17 '17 at 02:56

Aggregation using Project and Slice, mongodb

1 Answers1