mongodb distinct query values

Question

I have the following mongodb documents:

{
  "_id": "", 
  "name": "example1", 
  "colors": [
     {
         "id": 1000000,
         "properties": [
             {
                 "id": "1000",
                 "name": "",
                 "value": "green"
             },
             {
                 "id": "2000",
                 "name": "",
                 "value": "circle"
             }
         ]
     } ]
}
{
 "_id": "", 
  "name": "example2", 
  "colors": [
     {
         "id": 1000000,
         "properties": [
             {
                 "id": "1000",
                 "name": "",
                 "value": "red"
             },
             {
                 "id": "4000",
                 "name": "",
                 "value": "box"
             }
         ]
     } ]
}

I would like to get distinct queries on the value field in the array where id=1000

db.getCollection('product').distinct('colors.properties.value', {'colors.properties.id':{'$eq': 1000}})

but it returns all values in the array.

The expected Result would be:

["green", "red"]

Answer 1

There are a lot of way to do.

• $match eliminates unwanted data
• $unwind de-structure the array
• $addToSet in $group gives the distinct data

The mongo script :

db.collection.aggregate([
  {
    $match: {
      "colors.properties.id": "1000"
    }
  },
  {
    "$unwind": "$colors"
  },
  {
    "$unwind": "$colors.properties"
  },
  {
    $match: {
      "colors.properties.id": "1000"
    }
  },
  {
    $group: {
      _id: null,
      distinctData: {
        $addToSet: "$colors.properties.value"
      }
    }
  }
])

Working Mongo playground