MongoDB , getting the minimum & maximum of array subset

Question

Am trying to find a way to get the minimum number of orders between 2019-03-17 and 2019-03-19 excluding 2019-03-15 from the results ..

{ 
"_id" : ObjectId("5c8ffdadde62bf097d54ec47"), 
"productId" : "32886845998", 
"orders" : [
    {
        "date" : ISODate("2019-03-15T00:00:00.000+0000"), 
        "orders" : NumberInt(9)
    }, 
    {
        "date" : ISODate("2019-03-17T00:00:00.000+0000"), 
        "orders" : NumberInt(21)
    }, 
    {
        "date" : ISODate("2019-03-18T00:00:00.000+0000"), 
        "orders" : NumberInt(20)
    }, 
    {
        "date" : ISODate("2019-03-19T00:00:00.000+0000"), 
        "orders" : NumberInt(30)
    }
]

}

I tried using $min and $max operator but that didn't help because it iterated through the full array to find maximum & minimum

db.products.aggregate([
{
    $project: {
        maximum: {
            $reduce: {
                input: "$orders",
                initialValue: 0,
                in: {
                    $max: [
                        "$$value",
                        {
                            $cond: [
                                { $gte: [ "$$this.date", ISODate("2019-03-17T00:00:00.000+0000") ] },
                                "$$this.orders",
                                0
                            ]
                        }
                    ]
                }
            }
        }
    }
}

])

mickl · Accepted Answer · 2019-03-21 22:35:07Z

2

You can use $filter to apply filtering by orders.date and then you can apply $min and $max on filtered set:

db.col.aggregate([
    {
        $project: {
            filteredOrders: {
                $filter: {
                    input: "$orders",
                    cond: {
                        $and: [
                            { $gte: [ "$$this.date", ISODate("2019-03-17T00:00:00.000+0000") ] },
                            { $lte: [ "$$this.date", ISODate("2019-03-19T00:00:00.000+0000") ] },
                        ]
                    }
                }
            }
        }
    },
    {
        $project: {
            min: { $min: "$filteredOrders.orders" },
            max: { $max: "$filteredOrders.orders" },
        }
    }
])

answered Mar 21, 2019 at 22:35

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

1 Comment

dhyabi Over a year ago

Thanks mickl ,you are awesome as MongoDB

Collectives™ on Stack Overflow

MongoDB , getting the minimum & maximum of array subset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related