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.

turivishal · Accepted Answer · 2020-12-13 12:41:32Z

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

answered Dec 13, 2020 at 12:41

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB aggregation query based on multiple fields with similar values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related