Sort on values in array with mongoose-CodePudding

I got a schema for products. I want to be able to query them and sort on discount. The price is represented with an array called price, the latest element is the newest value. I.e I want the product with the highest discount first in the query.

Schema:

const mongoose = require('mongoose');

const ProductsSchema = new mongoose.Schema(
  {
    productName: {
      type: String,
    },
    price: [
      {
        date: {
          type: String,
        },
        value: {
          type: Number,
        },
      },
    ],
  },
  { collection: 'products' }
);
const products = mongoose.model('products', ProductsSchema);
module.exports = products;

CodePudding user response：

Query1

adds a field called discount
discount= priceN-princeN-1 (the difference between the last 2 prices), here is like the latest discount
if the product has < 2 prices discount = 0

Test code here

aggregate(
[{"$set":
  {"discount":
   {"$cond": 
    [{"$lte":[{"$size":"$price"}, 1]}, 0,
     {"$subtract":
      [{"$last":"$price.value"},
       {"$arrayElemAt":
        ["$price.value", {"$subtract":
                          [{"$size":"$price"}, 2]}]}]}]}}},
 {"$sort":{"discount":-1}}])

Query2

same like above
discount = lastPrice - minPrice

*you can replace $last with $max to get the max discount of all time

aggregate(
[{"$set":
  {"discount":
   {"$cond":
    [{"$lte":[{"$size":"$price"}, 1]}, 0,
     {"$subtract":
      [{"$last":"$price.value"}, {"$min":"$price.value"}]}]}}},
 {"$sort":{"discount":-1}}])