Array intersection in mongodb with Aggregation framework

Question

I have a collection with schema (mongoose) say :

{
  name : String,
  age  : Number,
  params : [Number]  // e.g. : params = [1,21,45,32,0] , usually they are very small arrays
}

The collection has 1000s of documents of this type

Say, I have a baseParams = [1,20,30,4,7];

I want to use aggregation and find the id of document whose params contain most number of numbers in baseParams array, like max(for each doc intersection(baseParams,params))

I finally need the _id of top 5 document sorted by age : 1

Any ideas anyone ?

Could you show the code you've tried? It's conceptually similar to this type of question: stackoverflow.com/questions/15635658/… — WiredPrairie
– WiredPrairie, Commented Jul 24, 2013 at 14:28

innoSPG · Accepted Answer · 2013-07-24 16:18:53Z

5

what about this (in the mongo shell)? Simply translate to mongoose

db.ss.aggregate([
   {$unwind: '$params'},
   {$match: {params: {$in: [1,20,30,4,7]} } },
   {$group: {_id: {_id:"$_id", age: "$age"}, nb: {"$sum":1} } },
   {$sort: {nb:-1}},
   {$limit:5},
   {$project: {_id:"$_id._id", age:"$_id.age", nb: "$nb"} },
   {$sort:{age:1}}
 ])

The first stage $unwind break up the array field so that you have for each _id, a number of documents equal to the number of elt in params, each with a single value of the array params. $match select the document corresponding to what we want. $group group them back using the _id and the age as key and count the number of doc in each group; this corresponds exactly to the number of element in the intersection. $limit take the top five. $project and $sort do the rest of the job in sorting by age

edited Jul 24, 2013 at 16:18

answered Jul 24, 2013 at 15:19

innoSPG

4,6561 gold badge31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WiredPrairie Over a year ago

If you could add details of how this works, you'd have a much better and more helpful answer.

innoSPG Over a year ago

This is the aggregation in mongo shell (I don't know anythink about mongoose). The first stage $unwind break up the array field so that you have for each _id, a number of document corresponding to the number of elt in params. $match select the document corresponding to what we want. $group group them back using the _id and the age as key and count the number of doc in each group; this correspond exactly to the number of element in the intersection. $limit take the top five. $project and $sort do the rest of the job in sorting by age

WiredPrairie Over a year ago

(If you can edit your answer to provide the details, that would be best!)

Collectives™ on Stack Overflow

Array intersection in mongodb with Aggregation framework

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related