CODEWITHSUNDEEP
mongodbaggregation-framework
bmilesp
bmilesp

Reputation: 2587

mongodb $project results in an array of array values

I have an orders table and a forms table. forms has many orders and i ran a successful $lookup with the following:

{
    from: 'orders',
    localField: 'slug',
    foreignField: 'form_slug',
    as: 'orders'
}

I then try to project like this:

{quantities : "$orders.line_items.quantity"}

The issue is that i get an array of nested arrays (see result below), when i really just want a sum total.

Is is possible to somehow $sum these values within the nested array?

"quantities": [
    [
        NumberLong(1)
    ],
    [
        NumberLong(1),
        NumberLong(1)
    ],
    [
        NumberLong(1)
    ],
    [
        NumberLong(1)
    ],
    [
        NumberLong(1)
    ],
    [
        NumberLong(1),
        NumberLong(1),
        NumberLong(1)
    ],
    [
        NumberLong(1)
    ],
    [
        NumberLong(1)
    ]
]

Upvotes: 0

Views: 559

Answers (2)

Akshay
Akshay

Reputation: 26

Query


    {
        "$lookup": {
            "from": "orders",
            "localField": "slug",
            "foreignField": "form_slug",
            "as": "orders"
        }
    }, {
        "$unwind": "$orders"
    }, {
        "$unwind": "$orders.line_items.quantities"
    }, {
        "$unwind": "$orders.line_items.quantities"
    }, {
        "$group": {
            "_id": "$_id",
            "sum": {
                "$sum": "$orders.line_items.quantities"
            }
        }
    }

Upvotes: 1

s7vr
s7vr

Reputation: 75914

You can try aggregation for Mongo 3.4 version

The below query uses $reduce with $concatArrays to reduce to array of values chaining with another $reduce to calculate the total.

{
 $project: {
        total: {
            $reduce: {
                input: {
                    $reduce: {
                        input: "$orders.line_items.quantity",
                        initialValue: [],
                        in: {
                            $concatArrays: ["$$value", "$$this"]
                        }
                    }
                },
                initialValue: 0,
                in: {
                    $add: ["$$value", "$$this"]
                }
            }
        }
    }
}

Mongo 3.2 and lower. Add the below stages after $lookup

{$unwind:"$orders"},
{$unwind:"$orders.line_items"},
{$unwind:"$orders.line_items.quantity"},
{$group:{"_id":null, total:{"$sum":"$orders.line_items.quantity"}}}

Upvotes: 1

Related Questions