Skip to content

How to group by sub documents and get unique value of value field?

This is my database collection:

{"productId" : 1,
"isVariant": 1,
"isComplete" : 1,
"variantId" : 1,
"attributeSet" : [ 
        {
            "name" : "Capacity",
            "value" : "500 GB",
            "id" : 3
        }, 
        {
            "name" : "Form Factor",
            "value" : "5 inch",
            "id" : 4
        }, 
        {
            "id" : 5,
            "name" : "Memory Components",
            "value" : "3D NAND",
            "isVariation" : 0
        }
    ]
},
{"productId" : 2,
"isVariant": 1,
"isComplete" : 1,
"variantId" : 1,
"attributeSet" : [ 
        {
            "name" : "Capacity",
            "value" : "1 TB",
            "id" : 3
        }, 
        {
            "name" : "Form Factor",
            "value" : "5 inch",
            "id" : 4
        }, 
        {
            "id" : 5,
            "name" : "Memory Components",
            "value" : "3D NAND",
            "isVariation" : 0
        }
    ]
},
{"productId" : 3,
"isVariant": 1,
"isComplete" : 0,
"variantId" : 1,
"attributeSet" : [ 
        {
            "name" : "Capacity",
            "value" : "500 GB",
            "id" : 3
        }, 
        {
            "name" : "Form Factor",
            "value" : "2.5 inch",
            "id" : 4
        }, 
        {
            "id" : 5,
            "name" : "Memory Components",
            "value" : "3D NAND",
            "isVariation" : 0
        }
    ]
},
{"productId" : 4,
"isVariant": 1,
"isComplete" : 0,
"variantId" : 1,
"attributeSet" : [ 
        {
            "name" : "Capacity",
            "value" : "1 TB",
            "id" : 3
        }, 
        {
            "name" : "Form Factor",
            "value" : "2.5 inch",
            "id" : 4
        }, 
        {
            "id" : 5,
            "name" : "Memory Components",
            "value" : "3D NAND",
            "isVariation" : 0
        }
    ]
}

Now I want to send the data of only the attribute where isVariation is not 0. Also I want to send the variant values of each attribute where isComplete =1. Hence the result should look like this

 result : [{
     "id": 3,
     "name": "Capacity",
     "value": [
         "500 GB",
         "1 TB"
     ]
  }, {
     "id": 4,
     "name": "Form Factor",
     "value": [
         "5 inch"
      ]
}]

The above result does not have value of 2.5 inch as the isComplete is 0 for this document. Can anyone help me with the query

Answer

  • $match isComplete is 1
  • $project to show required fields
  • $unwind deconstruct attributeSet array
  • $match attributeSet.isVariation is not 0
  • $group by attributeSet.id and get first name and get unique value using $addToSet
db.collection.aggregate([
  { $match: { isComplete: 1 } },
  {
    $project: {
      _id: 0,
      attributeSet: 1
    }
  },
  { $unwind: "$attributeSet" },
  { $match: { "attributeSet.isVariation": { $ne: 0 } } },
  {
    $group: {
      _id: "$attributeSet.id",
      name: { $first: "$attributeSet.name"  },
      value: { $addToSet: "$attributeSet.value" }
    }
  }
])

Playground

The $project stage is not required in your query, i have added because this will optimize your query performance.