database - Filter Out duplicate arrays and return the unique array in mongodb aggregation

Question

I have come a long way in structuring into the following mongodb data collection, but i couldn't finish the aggregation stage,

{
    "test": [
        {
            "_id": "60014aee808bc5033b45c222",
            "name": "a rogram",
            "companyName": "company NAme",
            "website": "https://www.example.comn",
            "loginUrl": "https://www.example.comn",
            "description": null,
            "createdBy": "5fe5cbcdb9ac0f001dccfadf",
            "createdAt": "2021-01-15T07:57:34.499Z",
            "updatedAt": "2021-01-15T13:09:09.417Z",
            "__v": 0,
            "address": null,
            "affiliatePlatform": "asdf",
            "brands": 3,
            "newList": [
                {
                    "_id": "5feee63fd86250046d64d02e",
                    "name": "NEWBRAND",
                    "affiliateProgram": "60014aee808bc5033b45c222",
                    "operator": "scdscacscasc",
                    "description": null,
                    "established": "2021-01-20T12:39:25.000Z",
                    "createdAt": "2021-01-01T09:07:11.180Z",
                    "updatedAt": "2021-01-15T12:39:36.898Z",
                    "__v": 0,
                    "updatedBy": null,
                    "newPMBList": [
                        {
                            "_id": "5feee78ead36a5052cdaddc0",
                            "projectMarket": "5feb10c4906c880076ce5fa6",
                            "brand": "5feee63fd86250046d64d02e",
                            "createdAt": "2021-01-01T09:12:46.588Z",
                            "updatedAt": "2021-01-01T09:12:46.588Z",
                            "__v": 0,
                            "newPMList": [
                                {
                                    "_id": "5feb10c4906c880076ce5fa6",
                                    "project": "5feb10b6906c880076ce5fa5",
                                    "market": "5feb0f44906c880076ce5f9d",
                                    "url": "https://www.example.com/place",
                                    "createdAt": "2020-12-29T11:19:32.945Z",
                                    "updatedAt": "2020-12-29T11:19:32.945Z",
                                    "__v": 0
                                }
                            ]
                        }
                    ]
                },
                {
                    "_id": "5ff07fce63da300174a014bb",
                    "name": "Nike",
                    "operator": "scdscacscasc",
                    "createdAt": "2021-01-02T14:14:38.607Z",
                    "updatedAt": "2021-01-15T08:05:09.475Z",
                    "__v": 0,
                    "description": "This is new afdkjnvlaf",
                    "established": "2021-01-27T08:02:38.000Z",
                    "updatedBy": null,
                    "affiliateProgram": "60014aee808bc5033b45c222",
                    "newPMBList": [
                        {
                            "_id": "5ff451891136a7006bc2b0eb",
                            "projectMarket": "5feb10c4906c880076ce5fa6",
                            "brand": "5ff07fce63da300174a014bb",
                            "createdAt": "2021-01-05T11:46:17.745Z",
                            "updatedAt": "2021-01-05T11:46:17.745Z",
                            "__v": 0,
                            "newPMList": [
                                {
                                    "_id": "5feb10c4906c880076ce5fa6",
                                    "project": "5feb10b6906c880076ce5fa5",
                                    "market": "5feb0f44906c880076ce5f9d",
                                    "url": "https://www.example.com/place",
                                    "createdAt": "2020-12-29T11:19:32.945Z",
                                    "updatedAt": "2020-12-29T11:19:32.945Z",
                                    "__v": 0
                                }
                            ]
                        }
                    ]
                },
                {
                    "_id": "5fff0336d78339005812aaa4",
                    "name": "United Bank",
                    "affiliateProgram": "60014aee808bc5033b45c222",
                    "operator": "scdsc",
                    "description": "ascasccsac",
                    "established": null,
                    "createdAt": "2021-01-13T14:27:02.931Z",
                    "updatedAt": "2021-01-15T12:43:34.857Z",
                    "__v": 0,
                    "updatedBy": null,
                    "newPMBList": []
                }
            ],
            "projectsNumber": null
        },
        {
            "_id": "6001923a1f1fb007437dc479",
            "name": "Hotel Advertisement",
            "companyName": "Atlas",
            "website": "https://www.atlas.com",
            "loginUrl": "https://www.atlas.com/luser/ogin",
            "createdBy": "5fe5cbcdb9ac0f001dccfadf",
            "createdAt": "2021-01-15T13:01:46.715Z",
            "updatedAt": "2021-01-15T13:20:42.757Z",
            "__v": 0,
            "address": null,
            "affiliatePlatform": "NetRefer",
            "description": null,
            "brands": 2,
            "newList": [
                {
                    "_id": "5ffff156f06d4700e255cde5",
                    "name": "Amazon",
                    "affiliateProgram": "6001923a1f1fb007437dc479",
                    "operator": "scdscacscasc",
                    "description": "vsdfvsfdv",
                    "established": null,
                    "createdAt": "2021-01-14T07:23:02.455Z",
                    "updatedAt": "2021-01-15T13:19:59.522Z",
                    "__v": 0,
                    "updatedBy": null,
                    "newPMBList": []
                },
                {
                    "_id": "60000d4c61316a01d2fbb1aa",
                    "name": "Toshiba",
                    "affiliateProgram": "6001923a1f1fb007437dc479",
                    "operator": "scdsc",
                    "description": "cvadfvfdvbfdav",
                    "established": null,
                    "createdAt": "2021-01-14T09:22:20.306Z",
                    "updatedAt": "2021-01-15T13:20:10.970Z",
                    "__v": 0,
                    "updatedBy": null,
                    "newPMBList": []
                }
            ],
            "projectsNumber": null
        }
    ]
}

in the document above i would like to only return the distinct list of the newPMList along with the other data except the newList array like so:

        "_id": "60014aee808bc5033b45c222",
        "name": "21 Program",
        "companyName": "Online Casino",
        "website": "https://www.online.casinoe",
        "loginUrl": "https://www.online.casino",
        "description": null,
        "createdBy": "5fe5cbcdb9ac0f001dccfadf",
        "createdAt": "2021-01-15T07:57:34.499Z",
        "updatedAt": "2021-01-15T13:09:09.417Z",
        "__v": 0,
        "address": null,
        "affiliatePlatform": "Cellxpert",
        "brands": 3,
        "newPMList": [
                            {
                                "_id": "5feb10c4906c880076ce5fa6",
                                "project": "5feb10b6906c880076ce5fa5",
                                "market": "5feb0f44906c880076ce5f9d",
                                "url": "https://www.addissoftware.com/ethiopia",
                                "createdAt": "2020-12-29T11:19:32.945Z",
                                "updatedAt": "2020-12-29T11:19:32.945Z",
                                "__v": 0
                            }
                        ]

per a document, but i couldn't any help is appreciated.

question from:https://stackoverflow.com/questions/65829706/filter-out-duplicate-arrays-and-return-the-unique-array-in-mongodb-aggregation

Answer 1

db.collection.aggregate([
  {//Denormalize first level
    "$unwind": "$newList"
  },
  {//Second nested level
    "$unwind": "$newList.newPMBList"
  },
  {//Deep nested last level
    "$unwind": "$newList.newPMBList.newPMList"
  },
  {
    $group: {//Grouping back
      "_id": null,
      "newList": {
        $push: "$newList.newPMBList.newPMList"
      }
    }
  },
  {
    $project: {//Finding unique
      newList: {
        $setUnion: [
          "$newList",
          "$newList"
        ]
      }
    }
  }
])

I suggest you to include other fields using first accumulator in group and preserve them in project.

You can simplify further as below

db.test.aggregate([
  {
    "$unwind": "$newList"
  },
  {
    "$unwind": "$newList.newPMBList"
  },
  {
    "$unwind": "$newList.newPMBList.newPMList"
  },
  {
    $group: {
      "_id": null,
      "newList": {//addToSet keeps distinct
        $addToSet: "$newList.newPMBList.newPMList"
      }
    }
  }
])

Further, buy skipping one denormalisation, But it returns array of arrays.

db.test.aggregate([
  {
    "$unwind": "$newList"
  },
  {
    "$unwind": "$newList.newPMBList"
  },
  {
    $group: {
      "_id": null,
      "newList": {
        $addToSet: "$newList.newPMBList.newPMList"
      }
    }
  }
])

If you skip another level, it will add one more nested level in the result.