Mongo query find top sellers from array

mongodb

Solution

You got most of the answer yourself, basically there are two ways to exclude the original item you were basing your query on. One is to simply add `{$match:{_id:{$ne:origID}}}` to the end of the pipeline. Don't forget to change `$limit:5` to `$limit:6` though, since you want to be left with five items after excluding the item itself.

A simpler way would be to add that same `{$match}` much earlier in the pipeline - in particular back after the `$unwind`. The entire pipeline (with some simplifications) should look like this:

db.orders.aggregate( 
    { $match   : { "order_line.ol_i_id" : 531 } } } },
    { $project : { "order_line" : 1, _id:0 } },
    { $unwind  : "$order_line"},
    { $match   : { "order_line.ol_i_id":{"$ne": 531 } } },
    { $group   : { _id : "$order_line.ol_i_id", 
                   totalSales : { $sum : "$order_line.ol_qty" } } },
    { $sort    : { totalSales : -1 } },
    { $limit   : 5 } 
);

Problem

I'm pretty new to Mongo, I'm having trouble figure out how to write this query. I have an order collection that looks like this. ``` { "_id" : ObjectId("51fade5b8c825bb19d9ef228"), "o_id" : 1, ... "order_line" : [ { "ol_id" : 1, "ol_o_id" : 1, "ol_i_id" : 531, "ol_qty" : 280 }, { "ol_id" : 2, "ol_o_id" : 1, "ol_i_id" : 90, "ol_qty" : 295 }, { "ol_id" : 3, "ol_o_id" : 1, "ol_i_id" : 963, "ol_qty" : 184 } ]} ``` I need to find the top selling related items for each item. The query needs to find the top 5 items/ol_i_id's that are sold WITH the ol_i_id that is currently being queried. In order to do so the query would need to find all orders with the "ol_i_id" in question, say "ol_i_id" : 531, then sum the "ol_qty" of each item sold with "ol_i_id" : 531 over the entire collection. Then report back with with the top 5 "ol_i_id" that are sold with "ol_i_id":531. I tried to make that as understandable as possible. /edit So far I have this. ``` db.orders.aggregate( { $match : { order_line: { $elemMatch : { ol_i_id : 531 } } } }, { $project : { o_id : 1, order_line : 1} }, { $unwind: "$order_line"}, { $limit : 5 } ) ``` Which unwinds the order_line 's to look like this. ``` "result" : [ { "_id" : ObjectId("51fade5b8c825bb19d9ef389 "o_id" : 354, "order_line" : { "ol_id" : 1, "ol_o_id" : 354, "ol_i_id" : 2, "ol_qty" : 271 } }, { "_id" : ObjectId("51fade5b8c825bb19d9ef389 "o_id" : 354, "order_line" : { "ol_id" : 2, "ol_o_id" : 354, "ol_i_id" : 707, "ol_qty" : 138 } }... ``` That query just gave me all the order_lines that we purchased with the ol_i_id in question, 531. Now I need to sum the ol_qty field for each unique ol_i_id and come back with the top 5. This would be analogous to something you might see on amazon where it says 'people who bought this also bought this other thing' Hope that makes more sense. Sorry for being overly verbose on this. Ideally, I would want it to come back with a set like this ``` { "result" : [ { "ol_i_id" : 46, "totalSoldWithItem531" : 20012 }, { "ol_i_id" : 669, "totalSoldWithItem531" : 19000 }, { "ol_i_id" : 5, "totalSoldWithItem531" : 18291 }, { "ol_i_id" : 881, "totalSoldWithItem531" : 18101 }, { "ol_i_id" : 538, "totalSoldWithItem531" : 17001 } ], "ok" : 1 } ``` /edit I have come up with this now which is almost what I need. ``` db.orders.aggregate( { $match : { order_line: { $elemMatch : { ol_i_id : 531 } } } }, { $project : { o_id : 1, order_line : 1} }, { $unwind: "$order_line"}, { $group : { _id : "$order_line.ol_i_id", totalSales : { $sum : "$order_line.ol_qty" } } }, { $sort : { totalSales : -1 } }, { $limit : 5 } ) ``` Results look like this. ``` { "result" : [ { "_id" : 531, "totalSales" : 10639 }, { "_id" : 655, "totalSales" : 520 }, { "_id" : 2, "totalSales" : 500 }, .... ``` My last problem is how would I exclude item that was queried from the result set since I'm not interested in that figure? In this case I need to exclude the _id : 531 from the results since that was the id that was queried.

Original source