I am querying data using the aggregate method in MongoDB, but I have a problem. Specifically, I want to replace the value of the price field (with the condition: if this field value is null) with the value of price_option field in the first item from the model field where stock_count is greater than 0. How can I do it?

My code to query collection with aggregate:

const doc = await ProductModel.aggregate([    
    ...
    {
      $group: {
        _id: '$_id',     
        price: { $first: "$price" },
        model: { 
          $addToSet: {
            price_option: '$model.priceOption',
            currency_unit: '$model.currencyUnit',
            stock_count: '$model.stockCount'            
          }        
        }
      }
    },
    {
      $project: {
        _id: 0,
        id: '$_id',       
        price: 1, 
        model: 1   
      }
    }
])

My sample data created in collection:

{
    "price": null,
    "model": [
      {
        "price_option": "200",
        "currency_unit": "dollar",
        "stock_count": 5 
      },
      {
        "price_option": "350",
        "currency_unit": "dollar",
        "stock_count": 0 
      },
      {
        "price_option": "400",
        "currency_unit": "dollar",
        "stock_count": 2 
      },
    ]
  }
]

My desired output is:

{
    "price": "200", <= it would be replace by the first item of remaining items where stock_count greater than 0

    "model": [
      {
        "price_option": "200",
        "currency_unit": "dollar",
        "stock_count": 5 
      },
      {
        "price_option": "350",
        "currency_unit": "dollar",
        "stock_count": 0 
      },
      {
        "price_option": "400",
        "currency_unit": "dollar",
        "stock_count": 2 
      },
    ]
}

CodePudding user response：

1. $set - Create price_model field.

   1.1. $filter to filter documents in model array which has stock_count $get 0.

   1.2. first to get the first document from the result in 1.1.

2. $project - Decorate the output documents. Assign price field with price_model.price_option.

db.collection.aggregate([
  {
    $set: {
      price_model: {
        $first: {
          "$filter": {
            "input": "$model",
            "cond": {
              $gt: [
                "$$this.stock_count",
                0
              ]
            }
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      id: "$_id",
      price: "$price_model.price_option",
      model: 1
    }
  }
])

Sample Mongo Playground