How to sort documents with optional field in mongoose?

Question

I have a product model where price is optional. The reason why price is optional is because product might have variations and in such case prices are specified in variations. The question is how to write products sorting in price ascending order by such logic: if there were variations in product then pick the lowest variation price to compare with other products, if there were no variation in product then take price property value of product to compare with other products.

My product schema:

interface IProductVariation extends IMongooseId { 
  price: number;
  //... some other properties
}

interface IProduct extends IMongooseId {
  /* price is optional because in case of variations presence,
   * prices will be specified in variations */
  price?: number;
  variations: IProductVariation[];
  //...some other properties  
}

const variationSchema = new Schema<IProductVariation>({
  price: { type: Number, required: true },
  // ...
});

const productSchema = new Schema<IProduct>({
  price: Number,
  variations: [variationSchema],
  // ...
});

method that queries data in repository (I need the proper sort):

  public async getPaginatedData(
    page: number,
    perPage: number,
    query: FilterQuery<IProduct> = {},
    sort?: FilterQuery<IProduct>
  ) {
    return this.model
      .find(query)
      .limit(perPage)
      .skip((page - 1) * perPage)
      .sort(sort);
  }

Answer 1

This way, the method will return products sorted by the lowest variation price if variations exist, and by the product price otherwise:

import { Aggregate, Model } from 'mongoose';

public async getPaginatedData(
    page: number,
    perPage: number,
    query: FilterQuery<IProduct> = {},
    sort?: FilterQuery<IProduct>
  ) {
    const aggregatePipeline: any[] = [
      { $match: query },
      { $project: {
          price: {
            $cond: {
              if: { $isArray: "$variations" },
              then: { $min: "$variations.price" },
              else: "$price"
            }
          },
          // Placeholder to mnetion other fields if needed
      }},
      { $sort: { price: 1 } }
    ];

    const aggregation: Aggregate<IProduct[]> = this.model.aggregate(aggregatePipeline);
    const totalCount: number = await this.model.countDocuments(query);

    const result = await aggregation
      .skip((page - 1) * perPage)
      .limit(perPage)
      .exec();

    return { result, totalCount };
  }