MongoDB - How to replace from null value with new values

Question

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 
      },
    ]
}

Yong Shun · Accepted Answer · 2022-02-21 00:04:26Z

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.
$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

For MongoDB version 4.2, you can use $arrayElementAt to get the first element of the array.

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

Sample Mongo Playground ($arrayElementAt)

edited Feb 21, 2022 at 0:04

answered Feb 20, 2022 at 12:53

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

A Little Girl Over a year ago

excuse me, what the version of MongoDB are you using?

Yong Shun Over a year ago

From MongoDB playground, it is version 5

A Little Girl Over a year ago

But $first is only available in the $group stage. It not works for Mongo version 4.2.2

Yong Shun Over a year ago

Hi, updated my answer. You can use $arrayElementAt.

Collectives™ on Stack Overflow

MongoDB - How to replace from null value with new values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related