CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Phorce
Phorce

Reputation: 4642

Mongodb Aggregate calculate average and add it to the document

I have websites which contains 2 documents:

{
    "_id" : ObjectId("58503934034b512b419a6eab"),
    "website" : "https://www.stackoverflow.com",
    "name" : "Stack Exchange",
    "keywords" : [ 
        "helping", 
        "C#", 
        "PYTHON"
    ]
}

{
    "_id" : ObjectId("58503934034b512b419a6eab"),
    "website" : "https://www.google.com.com",
    "name" : "Stack Exchange",
    "keywords" : [ 
        "search", 
        "engine", 
    ]
}

I also have another seo_tracking which contains:

{
    "_id" : ObjectId("587373d6f6325811c8a0b3ad"),
    "position" : "2",
    "real_url" : "https://www.stackoverflow.com",
    "created_at" : ISODate("2017-01-09T11:28:22.104Z"),
    "keyword" : "helping"
},
{
   "_id" : ObjectId("587373d6f6325811c8a0b3ad"),
   "position" : "4",
   "real_url" : "https://www.stackoverflow.com",
   "created_at" : ISODate("2017-01-09T11:28:22.104Z"),
   "keyword" : "C#"
}

etc.. This contains around 100+ documents

What I want to do is is aggregate the seo_tracking with website on the specific URL (www.stackexchange (in websites) would match www.stackoverflow.com in (seo_tracking)) which I can do fine. However, I would like to return for each of the websites the following:

{
    "_id" : ObjectId("587373d6f6325811c8a0b3ad"),
    "website":"https://www.stackoverflow.com", 
    "avg_position" : "2"
}

Then for Google etc.. Even if the avg_position is 0 .. I have tried the following:

db.seo_tracking.aggregate([

    {
        $lookup:
        {
            from: "websites", 
            localField: "real_url", 
            foreignField: "website", 
            as: "post_websites"
         },
     },
     {
        "$group": {
           _id:null, 
          avg_position:{$avg:"$position"}
          }
      } 
])

However, this just produces:

{
    "_id" : null,
    "avg_position" : 2.0
}

What I need to do is have website and ideally also need the ID

Any ideas to where I'm going wrong here?

Upvotes: 0

Views: 405

Answers (1)

s7vr
s7vr

Reputation: 75914

You can try something like this. You'll need to $unwind to access the fields from joined collection and change your grouping key to use the _id from joined collection to get average for each website:

db.seo_tracking.aggregate([{
    $lookup: {
        from: "website",
        localField: "real_url",
        foreignField: "website",
        as: "post_websites"
    },
}, {
    $unwind: "$post_websites"
}, {
    "$group": {
        _id: "$post_websites._id",
        avg_position: {
            $avg: "$position"
        },
        website: {
            $first: "$real_url"
        }
    }
}])

Upvotes: 2

Related Questions