CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-frameworkaggregate
AlexZeDim
AlexZeDim

Reputation: 4352

MongoDB aggregation with condition and unwind (one query instead of many)

I'd like to understand how this works:

I got collection with data like this:

  { _id: 5a854ee830093123a44bb7ec,
    item: 124103,
    owner: 'ZZZ',
    quantity: 50, //between 1 and X (X is fixed and I determine it before, in this case it's 50)
    lastModified: 1518685663000, //there are various lastModified intraday
    price: 116000,
    date: 2018-02-15 17:28:04.332 //default: new Date().toISOString()
  }

and aggregation script: 

    x.aggregate([
        {
            $project: { year: { $year: "$date" },
                month: { $month: "$date" },
                day: { $dayOfMonth: "$date" },
                item: "$item",
                owner: "$owner",
                quantity: "$quantity",
                lastModified: "$lastModified",
                price: "$price"}
        },
        {
            $match: { item: 124103, day: 15, month: 2, year: 2018, lastModified: 1518685663000}
        },
        //and here I use my group stage
        {
            $group:
               {
                   _id: "$item",
                   min_1: { $cond: { if: { $eq: [ "$quantity", 1] }, then: { $min: "$price" } } },
                   min_X: { $cond: { if: { $eq: [ "$quantity", X(50)] }, then: { $min: "$price" } } },
                 // then same for $max and $avg, (with different quantities) etc
                },
        },callback

What I want to receive:

in min_X field I'd like to receive $min:$price only if quantity is equal to X value (50).

Actually I could receive necessary data via many queries, (which I actually do right now) but I guess people in StackOverflow community knows how to achieve the same result via 1 query with $match and $unwind.

Yes, I have already read the docs, but understood only that I should your $cond operator. Guess someone could take me to the point up here, and provide relevant example. For example, in the future I'd like to operate with different $lastModified intraday, it means, I'd like to $group data after $project stage, then unwind cursor, and $group it with another $lastModified.

Upvotes: 0

Views: 1997

Answers (1)

Alex Blex
Alex Blex

Reputation: 37018

It's other way round $min, then $cond.

$group requires one of the accumulators for grouping fields. In your case it is $min. Then you can apply a condition $cond which value to use for aggregation. In case of $min you can use Number.MAX_VALUE for documents that don't match the criteria. It is always more or equal to any other value in javascript. You are free to use any sensible constant here.

After that you may want clean it up by replacing the junk numbers with nulls in another projection stage:

x.aggregate([
    .....
    { $group: {
        _id: "$item",                   
        min_1: { $min: { $cond: [ { $eq: [ "$quantity", 1] }, "$price", Number.MAX_VALUE ] } },
        min_50: { $min: { $cond: [ { $eq: [ "$quantity", 50] }, "$price", Number.MAX_VALUE ] } }
        // then same for $max and $avg, (with different quantities) etc
    } },
    { $project: {
        min_1: { $cond: [ { $eq: [ "$min_1", Number.MAX_VALUE] }, null, "$min_1" ] },
        min_50: { $cond: [ { $eq: [ "$min_50", Number.MAX_VALUE] }, null, "$min_50" ] }
    } }
])

Upvotes: 1

Related Questions