I have 3 collections with below schema

When I execute the below query am getting contractDetails with all voiceNumbers. How I can get only distinct voiceNumbers selected. Have tried multiple ways in a day but didn't land into desired output

db.getCollection("common").aggregate([
  {
    $match: {
      "name.firstName": "Kevin",
    },
  },
  {
    $lookup: {
      from: "user",
      localField: "_id",
      foreignField: "userId",
      as: "userDetails",
    },
  },
  {
    $unwind: {
      path: "$userDetails",
      includeArrayIndex: "0",
      preserveNullAndEmptyArrays: true,
    },
  },
  { $match: { "userDetails.userType": { $in: ["U"] } } },
  {
    $lookup: {
      from: "contract",
      localField: "userDetails.userNumber",
      foreignField: "userNumber",
      as: "contractDetails",
    },
  }
]);

Below are the Current & Expected result

CodePudding user response：

add below $unwind $group in the end of aggregate

  {
    $unwind: "$contractDetails"
  },
  {
    $group: {
      "_id": "$contractDetails.voiceNumber",
      "field": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    $group: {
      "_id": "$field._id",
      "userDetails": {
        "$first": "$field.userDetails"
      },
      "gender": {
        "$first": "$field.gender"
      },
      "name": {
        "$first": "$field.name"
      },
      "0": {
        "$first": "$field.0"
      },
      "contractDetails": {
        "$push": "$field.contractDetails"
      }
    }
  }

db.common.aggregate([
  {
    $match: {
      "name.firstName": "Kevin"
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "_id",
      foreignField: "userId",
      as: "userDetails"
    }
  },
  {
    $unwind: {
      path: "$userDetails",
      includeArrayIndex: "0",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $match: {
      "userDetails.userType": {
        $in: [
          "U"
        ]
      }
    }
  },
  {
    $lookup: {
      from: "contract",
      localField: "userDetails.userNumber",
      foreignField: "userNumber",
      as: "contractDetails"
    }
  },
  {
    $unwind: "$contractDetails"
  },
  {
    $group: {
      "_id": "$contractDetails.voiceNumber",
      "field": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    $group: {
      "_id": "$field._id",
      "userDetails": {
        "$first": "$field.userDetails"
      },
      "gender": {
        "$first": "$field.gender"
      },
      "name": {
        "$first": "$field.name"
      },
      "0": {
        "$first": "$field.0"
      },
      "contractDetails": {
        "$push": "$field.contractDetails"
      }
    }
  }
])

mongoplayground