Merging objects fields in MongoDB aggregation

[Robert N]

I tried many solutions with "mongodb aggregation", but without good solutions. If you have any solutions, thanks for yours help.

I have a document which looks like this:

{  
    "id" : 1,
    "sales" : {
      "1": {
        "count": 200,
        "A": {
            "count": 1,
        },
        "B": {
            "count": 2,
        }
        ...     
    },
     "2": {
        "count": 200,
        "A2": {
            "count": 1,
        },
        "B2": {
            "count": 2,
        }
        ...     
},
{
    "id" : 2,
    "sales" : {
      "1": {
        "count": 200,
        "A": {
            "count": 1,
        },
        "B": {
            "count": 2,
        }
        ...     
    },
     "2": {
        "count": 200,
        "A2": {
            "count": 1,
        },
        "B2": {
            "count": 2,
        }
        ...     
}




I want to group by count and the number of instances of "sales" and "refs". Something like this:

{
    "sales" : [
    {
        "title" : "1",
        "count" : 400,
        "refs" : [
            {
                "ref" : "A",
                "count" : 2
            },
            {
                "ref" : "B",
                "count" : 4
            }               
        ]
    },
    {
        "title" : "2",
        "count" : 400,
        "refs" : [
            {
                "ref" : "A2",
                "count" : 2
            },
            {
                "ref" : "B2",
                "count" : 4
            }               
        ]
    }       
 ]

}

Many thanks,

Robert 

[Gaurav Gupta]

Hi Robert,

Based on your desired aggregation output, you’re intending to group by document field name. i.e. “A”, “A2”, etc. You need to utilise $objectToArray aggregation operator to pivot the field key into value before you’re able to pass it on to $group stage.

For example, try below aggregation pipeline to see what $objectToArray would do :

db.collection.aggregate([
    {$project:{"tmp1":{$objectToArray:"$sales"}}},
    {$unwind:"$tmp1"}
    ]).pretty()

Having said the above, I would recommend to reconsider your document data model. Using a simpler schema will also simplify your query.

You may also find MongoDB Use Case: Product Data Management as a useful reference.

Regards,
Gaurav

​