How to count distinct object located multiple in multiple arrays

Question

Hi i have the following model:

{
    "_id" : ObjectId("5d65a9c2f62b0d437bedc87b"),
    "name" : "dsadasdsadsadsa",
    "companyBases" : [
        {
            "vehicles" : [ 
                {
                    "_id": "unique1"
                },
                {
                    "_id":"unique1"
                },
                {
                    "_id":"unique2"
                }
            ],
            "_id" : ObjectId("5d65aef598b4734a8d3994b1"),
            "name" : "Tech Parking 22",
            "__v" : 0
        },
        {
            "vehicles" : [ 
                {
                    "_id": "unique1"
                },
                {
                    "_id":"unique2"
                }
            ],
            "_id" : ObjectId("5d65aef598b4734a8d3994b3"),
            "name" : "Tech Parking 23",
            "__v" : 0
        }
    ],
    "__v" : 0
}

I would like to count distinct objects ("_id" in "vehicles" is distinct value) in "vehicles" array in "companyBases" array.

In above example pattern on counting should be:

"unique1" - 3, "unique2" - 2

So it gives 2 unique objects in "vehicles" array for whole array "companyBases".

So the output should be:

{
    "count": 2
}

Answer 1

The following query can do the trick:

db.collection.distinct("companyBases.vehicles._id").length

Answer 2

You can use $reduce to scan outer array and $setUnion to get an array of unique values from two other input arrays. Then you just need $size to get the length of that array:

db.collection.aggregate([
    {
        $project: {
            unique: {
                $reduce: {
                    input: "$companyBases",
                    initialValue: [],
                    in: {
                        $setUnion: [
                            "$$value",
                            "$$this.vehicles._id"
                        ]
                    }
                }
            }
        }
    },
    {
        $project: {
            count: { $size: "$unique" }
        }
    }
])

Mongo Playground