MongoDB aggregation query based on multiple fields with similar values

Question

I have documents that look like this:

{
    "_id" : "001",
    "a" : {
        "b" : {
            "c" : {
                "custId" : "cust1"
            },
            "d" : {
                "custId" : "cust2"
            }
        }
    }
}
{
    "_id" : "002",
    "a" : {
        "b" : {
            "c" : {
                "custId" : "cust1"
            },
            "d" : {
                "custId" : "cust3"
            }
        }
    }
}
{
    "_id" : "003",
    "a" : {
        "b" : {
            "c" : {
                "custId" : null
            },
            "d" : {
                "custId" : "cust2"
            }
        }
    }
}
{
    "_id" : "004",
    "a" : {
        "b" : {
            "c" : {
                "custId" : null
            },
            "d" : {
                "custId" : "cust1"
            }
        }
    }
}

I would like to obtain an aggregation which shows a sorted count of customer ids, ignoring null customer ids, like this:

{
    "_id" : "cust1",
    "count" : 3,
    "records" : [ 
        "001", "002", "004"
    ]
}
{
    "_id" : "cust2",
    "count" : 2,
    "records" : [ 
        "001", "003"
    ]
}
{
    "_id" : "cust3",
    "count" : 1,
    "records" : [ 
        "002"
    ]
}

I think each document needs to be broken down into 1 or 2 customer based arrays than then unwound back into documents, but I have been unable to determine a workable solution.

Answer 1

• make an array of custId, $map to iterate loop of b after converting from object to array using $objectToArray
• $unwind deconstruct custIds array
• $match to filter none null custIds documents
• $group by custIds and get count of total records and make unique array of _id using $addToset

db.collection.aggregate([
  {
    $project: {
      custIds: {
        $map: {
          input: { $objectToArray: "$a.b" },
          in: "$$this.v.custId"
        }
      }
    }
  },
  { $unwind: "$custIds" },
  { $match: { custIds: { $ne: null } } },
  {
    $group: {
      _id: "$custIds",
      count: { $sum: 1 },
      records: { $addToSet: "$_id" }
    }
  }
])

Playground