Compare documents in a collection-CodePudding

Consider following collection named sales:

[
  {
    product: "Banana",
    timestamp: 1672992000,
    price: 5,
  },
  {
    product: "Banana",
    timestamp: 1672992001,
    price: 6,
  },
  {
    product: "Pineapple",
    timestamp: 1672992000,
    price: 9,
  },
  {
    product: "Pineapple",
    timestamp: 1672992001,
    price: 8,
  },
  {
    product: "Melon",
    timestamp: 1672992005,
    price: 15,
  },
  
]

How do we query product sales that have higher consecutive prices?

In our example it would be banana with price of 6.

not melon, as we don't have anything to compare to
not pineapple, as the later sale has lower price, than the previous one

Obviously there could be more than 2 sales per product.

Is it possible to do with aggregation?

CodePudding user response：

db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": "$product",    //for each product,
      "sortBy": {timestamp: 1},     //sort by timestamp
      "output": {
        "lPrice": {
          $max: "$price",           //add a field whose value is price value of next document
          "window": {
            "documents": [1,1]
          }
        }
      }
    }
  },                               //output of this stage has current price and next price
  {
    $match: {
      $expr: {
        $gt: ["$lPrice", "$price"] //filter the documents where next price is higher than current price
      }
    }
  }
]);

Playground