"SelectMany" - equivalent in mongodb aggregation pipeline

user_2879579 0
Viewed 144

Given the following documents:

/* 1 */
{
    "_id" : ObjectId("5f38fb2b52e28012a8f175f9"),
    "date" : "2020-08-16",
    "values" : {
        "wert1" : 5.0,
        "wert2" : 9.0
    }
}

/* 2 */
{
    "_id" : ObjectId("5f38fb2b52e28012a8f175fa"),
    "date" : "2020-08-16",
    "values" : {
        "wert1" : 3.0,
        "wert3" : 10.0
    }
}

How can I transform this into something like the following:

{ "wert1": [ { "2020-08-16": "8.0" } ] },
{ "wert2": [ { "2020-08-16": "9.0" } ] },
{ "wert3": [ { "2020-08-16": "10.0" } ] }

In theory in would be a:

  • "SelectMany" $values.$key
  • Group by $key and $date with $sum of values
  • Transform

What is the operator for this "SelectMany"?

aggregation-framework mongodb aggregate
1 Answers

Typically $unwind would act as SelectMany. In your case it's a little bit more complicated since you want to aggregate by key names so you also need $objectToArray and $arrayToObject operators:

db.collection.aggregate([
    {
        $project: {
            date: 1,
            values: { $objectToArray: "$values" }
        }
    },
    { $unwind: "$values" },
    {
        $group: {
            _id: { date: "$date", key: "$values.k" },
            total: { $sum: "$values.v" }
        }
    },
    {
        $group: {
            _id: "$_id.key",
            v: { $push: { $arrayToObject: [ [ { k: "$_id.date", v: "$total" } ] ] } }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: [ [ { k: "$_id", v: "$v" } ] ]
            }
        }
    }
])

Mongo Playground

user_62389770
Related