CODEWITHSUNDEEP
mongodbmongooseaggregate
toms25
toms25

Reputation: 87

Is it possible to aggregate with multiple conditions on mongodb

I have a pricing collection and i want to query the biggest price change over time depending on the seller and i have not clue how to accomplish this in a single query.

The collection includes 

_id
partNumber
seller
price
createdAt

Every day for each product about 30 prices should get inserted, but in a real world that is not 100%...

so i was thinking about this.

  1. get the first price where date <= (currentDate - compare range days) for partNumber and seller
  2. get the newest price for partNumber and seller
  3. diff the prices
  4. sort by diff

Is that possible with mongodb aggregate or something else?

Upvotes: 1

Views: 47

Answers (1)

Jitendra
Jitendra

Reputation: 3185

Let assume we have 2 sellers A and B. We have taken a set of data as below (assuming part Number are same or you can change it as well): 

/* 1 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb442"),
    "partNumber" : 1,
    "seller" : "B",
    "price" : 15,
    "createdAt" : ISODate("2019-10-11T16:13:31.451+05:30")
},

/* 2 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb441"),
    "partNumber" : 1,
    "seller" : "A",
    "price" : 1,
    "createdAt" : ISODate("2019-10-11T16:13:31.451+05:30")
},

/* 3 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb440"),
    "partNumber" : 1,
    "seller" : "B",
    "price" : 10,
    "createdAt" : ISODate("2019-10-10T16:13:31.451+05:30")
},

/* 4 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb43f"),
    "partNumber" : 1,
    "seller" : "A",
    "price" : 10,
    "createdAt" : ISODate("2019-10-10T16:13:31.451+05:30")
},

/* 5 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb43e"),
    "partNumber" : 1,
    "seller" : "B",
    "price" : 7,
    "createdAt" : ISODate("2019-10-09T16:13:31.451+05:30")
},

/* 6 createdAt:18/10/2019, 16:48:35*/
{
    "_id" : ObjectId("5da99f8bd4ce6a7d8bebb43d"),
    "partNumber" : 1,
    "seller" : "A",
    "price" : 5,
    "createdAt" : ISODate("2019-10-09T16:13:31.451+05:30")
}

Our Query will be as below: 

db.collection.aggregate([
    {
        $group: {
            _id: { seller: "$seller", partNumber: "$partNumber" },
            lastSalesPrice: { $last: "$price" },
            firstSalesPrice: { $first: "$price" },
        }
    },
    {
        $project: {
            seller: "$_id.seller",
            partNumber: "$_id.partNumber",
            lastSalesPrice: "$lastSalesPrice",
            firstSalesPrice: "$firstSalesPrice",
            diff: { $subtract: ["$lastSalesPrice", "$firstSalesPrice" ] }
        }
    },
    {
        $sort: { "diff": -1 }
    }
])

Result of above query will be as follow:

/* 1 */
{
    "_id" : {
        "seller" : "B",
        "partNumber" : 1
    },
    "seller" : "B",
    "partNumber" : 1,
    "lastSalesPrice" : 15,
    "firstSalesPrice" : 7,
    "diff" : 8
},

/* 2 */
{
    "_id" : {
        "seller" : "A",
        "partNumber" : 1
    },
    "seller" : "A",
    "partNumber" : 1,
    "lastSalesPrice" : 1,
    "firstSalesPrice" : 5,
    "diff" : -4
}

Upvotes: 1

Related Questions