CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Ran Alcobi
Ran Alcobi

Reputation: 209

$group with $lookup

I have a question in MongoDB query. I have the following users collection:

{
    "_id" : ObjectId("5aa03bf97d6e1d28a020f488"),
    "name":"A1",
     "interests" : [ 
        ObjectId("5aa03b877d6e1d28a020f484"), 
        ObjectId("5aa03bb47d6e1d28a020f485")
    ]
},
{
    "_id" : ObjectId("5affd69339f67335303ddf77"),
    "name":"A2",
     "interests" : [ 
        ObjectId("5aa03b877d6e1d28a020f484")
    ]
},
{
    "_id" : ObjectId("5affd69339f673ddfjfhri45"),
    "name":"A3",
     "interests" : [ 
        ObjectId("5aa03bb47d6e1d28a020f485"), 
    ]
},
{
    "_id" : ObjectId("5affd69339f67365656ddfg4f"),
    "name":"A4",
     "interests" : [ 
        ObjectId("5aa16eb8890cbb4c582e8a38"), 
    ]
}

The interests collection look li the following example:

{
    "_id" : ObjectId("5aa16eb8890cbb4c582e8a38"),
    "name" : "Swimming",
},
{
    "_id" : ObjectId("5aa03bb47d6e1d28a020f485"),
    "name" : "Basketball",
},
{
    "_id" : ObjectId("5aa03b877d6e1d28a020f484"),
    "name" : "Fishing",
}

I want to write a query that counts for the interests types of all users: the expected result is like:

 [
    {
        "name":"fishing"
        "count":21 
    },
    {
        "name":"Basketball"
        "count":15
    }
]

Thanks for helpers :)

Upvotes: 1

Views: 95

Answers (2)

Ashh
Ashh

Reputation: 46441

If you have mongodb 3.6 then you can try below aggregation

db.collection.aggregate([
  { "$lookup": {
    "from": Intrest.collection.name,
    "let": { "interests": "$interests" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": [ "$_id", "$$interests" ] },
        "name": "Swimming",
      }}
    ],
    "as": "interests"
  }},
  { "$unwind": "$interests" },
  { "$group": {
    "_id": "$interests.name",
    "count": { "$sum": 1 }
  }},
  { "$project": {
    "name": "$_id", "count": 1
  }}
])

And if you want to group with interests name

db.collection.aggregate([
  { "$lookup": {
    "from": Intrest.collection.name,
    "let": { "interests": "$interests" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$interests" ] } }}
    ],
    "as": "interests"
  }},
  { "$unwind": "$interests" },
  { "$group": {
    "_id": "$interests.name",
    "count": { "$sum": 1 }
  }},
  { "$project": {
    "name": "$_id", "count": 1
  }}
])

Upvotes: 2

Dženis H.
Dženis H.

Reputation: 7822

 db.collection.aggregate(
  {
    $group: {
      _id: '$interests'
    }
  },
  {
    $group: {
      _id: '$name',
      count: {
        $sum: 1
      }
    }
  }
)

Upvotes: 0

Related Questions