MongoDB - Document query with the field value dynamically (Condition with dynamic date)

Question

const orders = await db1.db.collection("order_all").find({
    progress_status_kd: { $in: [32, 33] },
    ps_screen_preparing_n_packaging_permission: true,
    $expr: {
        $lte: [
            "$progress_status_kd_updated_at",
            // "2025-05-07 13:59:40" from this condition bellow it should return date format like this, it works if I put hardcoded date but not from this $cond
            {
                $cond: [
                    { $eq : ["$progress_status_kd", 33] },
                    moment().subtract("$kdsConfig.ps_screen_preparing_time", 'hours'),
                    moment().subtract("$kdsConfig.ps_screen_packaging_time", 'minutes')
                ]
            }
        ],
    }
}).toArray();

I want to subtract a dynamic value based on a condition. If progress_status_kd === 33, it should subtract with the ps_screen_preparing_time value from kdsConfig. How can I achieve this, as currently it is not working correctly as expected?

Here is my MongoDB data:

{
    "_id": ObjectId("681a0a555133c90d813dac22"),
    "kdsConfig": {
        "ps_screen_packaging_time" : 10,
        "ps_screen_preparing_time" : 10
    },
    "progress_status_kd": 33,
    "progress_status_kd_updated_at": "2025-05-07 14:00:00",
    "ps_screen_preparing_n_packaging_permission": true
}

Yong Shun · Accepted Answer · 2025-05-08 05:44:36Z

4

You can't use moment as it cannot be constructed in the query to obtain the field value for a real-time query.

Instead, you should use $dateAdd operator, $$NOW (current date time), and set the negative value to do subtraction.

For MongoDB version 5.0 and above:

db.collection.find({
  progress_status_kd: {
    $in: [
      32,
      33
    ]
  },
  ps_screen_preparing_n_packaging_permission: true,
  $expr: {
    $lte: [
      "$progress_status_kd_updated_at",
      {
        $cond: [
          {
            $eq: [
              "$progress_status_kd",
              33
            ]
          },
          {
            $dateAdd: {
              startDate: "$$NOW",
              unit: "hour",
              amount: {
                $multiply: [
                  -1,
                  "$kdsConfig.ps_screen_preparing_time"
                ]
              }
            }
          },
          {
            $dateAdd: {
              startDate: "$$NOW",
              unit: "minute",
              amount: {
                $multiply: [
                  -1,
                  "$kdsConfig.ps_screen_packaging_time"
                ]
              }
            }
          }
        ]
      }
    ]
  }
})

Demo @ MongoPlayground

Alternatively, you can use $dateSubtract operator which is more directly.

db.collection.find({
  progress_status_kd: {
    $in: [
      32,
      33
    ]
  },
  ps_screen_preparing_n_packaging_permission: true,
  $expr: {
    $lte: [
      "$progress_status_kd_updated_at",
      // "2025-05-07 13:59:40" from this condition bellow it should return date format like this, it works if I put hardcoded date but not from this $cond
      {
        $cond: [
          {
            $eq: [
              "$progress_status_kd",
              33
            ]
          },
          {
            $dateSubtract: {
              startDate: "$$NOW",
              unit: "hour",
              amount: "$kdsConfig.ps_screen_preparing_time"
            }
          },
          {
            $dateSubtract: {
              startDate: "$$NOW",
              unit: "minute",
              amount: "$kdsConfig.ps_screen_packaging_time"
            }
          }
        ]
      }
    ]
  }
})

Demo @ MongoPlayground

edited May 8 at 5:44

answered May 7 at 12:13

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

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

11 Comments

Yong Shun May 7 at 12:40

You should use $toDate like this demo

Yong Shun May 7 at 12:59

You paste the document in Mongo Playground, then you try to adjust the date. It is hard to know what's not working in your side without knowing what is the expected document that you want to get.

suvro May 7 at 13:01

Hey man you rocks! It's working perfectly. I changed the time from the playground and it's working as expect. Sorry for misunderstanding. You are really cool. Take love bro.

Wernfried Domscheit May 7 at 16:40

$dateSubtract operator was also added in MongoDB version 5.0. No need for version 8.0

Wernfried Domscheit May 7 at 16:55

Date values should never be stored as string, it's a design flaw. And your case is a justification of my statement.

|

Collectives™ on Stack Overflow

MongoDB - Document query with the field value dynamically (Condition with dynamic date)

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related