node.jsmongodbmongodb-query

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


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
}

Solution

  • 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