Mongo get Distinct Value Pairs and a List of Values of a thrid attribute

Question

I have the following problem:

I want to get the following information from a MongoDB using the Python driver:

• A list of two unique combinations of two attributes and a sublist of a third value with the attributes of the third value.

Input:

[
  { surname: "Will", lastname: "Smith", position:"Actor" },
  { surname: "Michael", lastname: "Jackson", position:"Musican" },
  { surname: "Will", lastname: "Smith", position:"Musican" },
  { surname: "Alicia", lastname: "Keys", position:"Musican" },
  { surname: "Brad", lastname: "Pitt", position:"Actor" }
];

My actual Query:

result = client['coll']['db'].aggregate([
    {
        '$group': {
            '_id': {
                'surname': '$surname', 
                'lastname': '$lastname'
            }
        }
    }
])

Output of my Query

[
  { surname: "Will", lastname: "Smith"},
  { surname: "Michael", lastname: "Jackson"},
  { surname: "Alicia", lastname: "Keys"},
  { surname: "Brad", lastname: "Pitt"}
]

Desired Output:

[
  { surname: "Will", lastname: "Smith", position: {["actor","musican"]}},
  { surname: "Michael", lastname: "Jackson", position:{["musican"]}},
  { surname: "Alicia", lastname: "Keys", position:{["musican"]}},
  { surname: "Brad", lastname: "Pitt", position:{["actor"]}}
]

Answer 1

Your desired output is not a valid JSON so i'll assume you meant the following.

You just need to use $addToSet while grouping to get all unique positions, like so:

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "surname": "$surname",
        "lastname": "$lastname"
      },
      positions: {
        $addToSet: "$position"
      }
    }
  }
])

Mongo Playground