Mongo Query to fetch distinct nested documents

Question

I need to fetch distinct nested documents.

Please find the sample document:

{
    "propertyId": 1001820437,
    "date": ISODate("2020-07-17T00:00:00.000Z"),
    "HList":[
        {
            "productId": 123,
            "name": "Dubai",
            "tsh": true
        }
    ],
    "PList":[
        {
            "productId": 123,
            "name": "Dubai",
            "tsh": false
        },
        {
            "productId": 234,
            "name": "India",
            "tsh": true
        }
    ],
    "CList":[
        {
            "productId": 234,
            "name": "India",
            "tsh": false
        }
    ]
}

Expected result is:

{
    "produts":[
        {
            "productId": 123,
            "name": "Dubai"
        },
        {
            "productId": 234,
            "name": "India"
        }
    ]
}

I tried with this query:

 db.property.aggregate([
    { 
        $match: { 
            "propertyId": 1001820437,
            "date": ISODate("2020-07-17T00:00:00.000Z")
        }
    },
    { 
        "$project": {
            "_id": 0,
            "unique": {
                "$filter": {    
                    "input": {
                        "$setDifference": [
                            { 
                                "$concatArrays": [
                                    "$HList.productId",
                                    "$PList.productId",
                                    "$CList.productId"
                                ]
                            },
                            []
                        ]  
                    },
                    "cond": { 
                        "$ne": [ "$$this", "" ] 
                    }
                }
            }
        }
    }
]);

Is $setDifference aggregation is correct choice here?
My query returns only unique product ids but i need a productId with name. Could someone help me to solve this? Thanks in advance

Answer 1

You can use $projectfirst to get rid of tsh field and then run $setUnion which ignores duplicated entries:

db.collection.aggregate([
    {
        $project: {
            "HList.tsh": 0,
            "PList.tsh": 0,
            "CList.tsh": 0,
        }
    },
    {
        $project: {
            products: {
                $setUnion: [ "$HList", "$PList", "$CList" ]
            }
        }
    }
])

Mongo Playground