CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Hardik Shah
Hardik Shah

Reputation: 4210

Multiple group - Averaging of each value inside array without duplicity from another document

I have added three JSON sample data for better understanding:

{
    "_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
    "claim" : ObjectId("5b927bd9c725181a3dc9107e"),
    "user" : ObjectId("5b869a5d0d56de9d788edb7e"),
    "comprisedPhrase" : [
        {
            "phrase" : "ABC",
            "like" : 1,
            "dislike" : 0
        }, 
        {
            "phrase" : "DEF",
            "like" : 0,
            "dislike" : 1
        }, 
        {
            "phrase" : "GHI",
            "like" : 1,
            "dislike" : 0
        }
    ], 
},
{
    "_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
    "claim" : ObjectId("5b927bd9c725181a3dc9107e"),
    "user" : ObjectId("5b869a5d0d56de9d788edb7a”),
    "comprisedPhrase" : [
        {
            "phrase" : "ABC",
            "like" : 1,
            "dislike" : 0
        }, 
        {
            "phrase" : "DEF",
            "like" : 0,
            "dislike" : 1
        }, 
        {
            "phrase" : "GHI",
            "like" : 1,
            "dislike" : 0
        }
    ], 
},
{
    "_id" : ObjectId("5b961bdfd6588fed3fd8e2a9"),
    "claim" : ObjectId("5b927bd9c725181a3dc9107a”),
    "user" : ObjectId("5b869a5d0d56de9d788edb7a”),
    "comprisedPhrase" : [
        {
            "phrase" : "ABC",
            "like" : 0,
            "dislike" : 1
        }, 
        {
            "phrase" : "DEF",
            "like" : 1,
            "dislike" : 0
        }, 
        {
            "phrase" : "GHI",
            "like" : 1,
            "dislike" : 0
        },
        {
            "phrase" : "JKL",
            "like" : 0,
            "dislike" : 0
        }
    ], 
}

Expected output:

/* 1 */
{
    "_id" : ObjectId("5b927bd9c725181a3dc9107e"), // Claim id
    "comprisedPhrase" : [ 
        {
            "phrase" : "ABC",
            "totalCount" : 2.0,
            "sumLike" : 2,
            "sumDislike" : 0
        }, 
        {
            "phrase" : "DEF",
            "totalCount" : 2.0,
            "sumLike" : 0,
            "sumDislike" : 2
        },
        {
            "phrase" : "GHI",
            "totalCount" : 2.0,
            "sumLike" : 2,
            "sumDislike" : 0
        }
    ],
    "totalCount" : 2.0
}

/* 2 */
{
    "_id" : ObjectId("5b927bd9c725181a3dc9107a"),
    "comprisedPhrase" : [ 
        {
            "phrase" : "ABC",
            "totalCount" : 1.0,
            "sumLike" : 0,
            "sumDislike" : 1
        },
        {
            "phrase" : "DEF",
            "totalCount" : 1.0,
            "sumLike" : 1,
            "sumDislike" : 0
        },
        {
            "phrase" : "GHI",
            "totalCount" : 1.0,
            "sumLike" : 1,
            "sumDislike" : 0
        },
        {
            "phrase" : "JKL",
            "totalCount" : 1.0,
            "sumLike" : 0,
            "sumDislike" : 0
        }
    ],
    "totalCount" : 1.0
}

What, I have tried So far:

db.getCollection(‘anydb’).aggregate([{
  {
    "$unwind": "$comprisedPhrase"
  },
  {
      "$group" : {
        _id: "$comprisedPhrase.phrase",
        claimId: { "$first" : "$claim._id"},
        totalCount: { "$sum": 1 },
        sumLike : {"$sum" : "$comprisedPhrase.like"},
        sumDislike : {"$sum" : "$comprisedPhrase.dislike"}
    }
  },{
      "$group" : {
        _id: "$claimId",
        comprisedPhrase: { "$push" : { phrase: "$_id", totalCount: "$totalCount", sumLike: "$sumLike", sumDislike: "$sumDislike" }},
        totalCount: { "$sum": 1 }
    }
  }
  ])

But, this will count ABC from different claims too. And it is obvious.

I just want to group By claim id. and for each claim, there is the list of words. Those list of words should give me the sum of like and dislike or averaging the like and dislike with inside totalCount.

Note: MongoDB V3.2.18

Upvotes: 2

Views: 55

Answers (2)

AJ91
AJ91

Reputation: 140

Your second stage should make _id as both $comprisedPhrase.phrase and $claim._id.

So the final query should look like this:

db.getCollection(‘anydb’).aggregate([{
  {
    "$unwind": "$comprisedPhrase"
  },
  {
      "$group" : {
        _id: {"phrase": "$comprisedPhrase.phrase", "claimId":"$claim._id"},
        //claimId: { "$first" : "$claim._id"},
        totalCount: { "$sum": 1 },
        sumLike : {"$sum" : "$comprisedPhrase.like"},
        sumDislike : {"$sum" : "$comprisedPhrase.dislike"}
    }
  },{
      "$group" : {
        _id: "$_id.claimId",
        comprisedPhrase: { "$push" : { phrase: "$_id.phrase", totalCount: "$totalCount", sumLike: "$sumLike", sumDislike: "$sumDislike" }},
        totalCount: { "$sum": 1 }
    }
  }
  ])

Hope this helps. Thanks.

Upvotes: 0

Ashh
Ashh

Reputation: 46481

You can try below aggregation

db.collection.aggregate([
  { "$unwind": "$comprisedPhrase" },
  { "$group": {
    "_id": { "phrase": "$comprisedPhrase.phrase", "claimId": "$claim" },
    "sumLike": { "$sum": "$comprisedPhrase.like" },
    "sumDislike": { "$sum": "$comprisedPhrase.dislike" },
    "count": { "$sum": 1 }
  }},
  { "$group": {
    "_id": "$_id.claimId",
    "totalCount": { "$first": "$count" },
    "comprisedPhrase": {
      "$push": {
        "phrase": "$_id.phrase",
        "sumLike": "$sumLike",
        "sumDislike": "$sumDislike",
        "totalCount": "$count"
      }
    }
  }}
])

Upvotes: 1

Related Questions