MongoDB Query array based on value of another field

Question

I need to be able to find any conditions inside an array of a document in my collection based on the value of another field.
My document:

{
    "totalSteps": 3,
    "currentStep": 2,
    "status": "submitted",
    "completed": false,
    "completedDate": null,
    "orderBody": [
        {
            "status": "complete",
            "stepStarted": 1617207419303,
            "stepEnded": "",
            "executionOutput": ""
        },
        {
            "status": "incomplete",
            "stepStarted": 1617211111113,
            "stepEnded": "",
            "executionOutput": ""
        },
        {
            "status": "incomplete",
            "stepStarted": 1617207419303,
            "stepEnded": "",
            "executionOutput": ""
        }
    ],

}

My query:

           ...find($and: [
            { orderBody: {$elemMatch: { "stepStarted" : { $lte: currentTime }, status : "incomplete"}}},
            {status: { $ne: "failed"}}
          ])

My Issue: I need the document returned only if the value of (currentStep - 1) is the same as the matched array. Right now the query will return the document because the conditions of orderBody[2] are fulfilled. Notice the stepStarted of orderBody[2] is < orderBody[1]. currentTime is a variable passed from server in another section of code.

I've tried:

           $and: [
            { currentStep:{ {$indexOfArray: {orderBody: {$elemMatch: { "stepStarted" : { $lte: currentTime }, status : "incomplete"}}} - 1}},
            {status: { $ne: "failed"}}
          ]

           $and: [
            { currentStep: { $eq: {$indexOfArray: {orderBody: {$elemMatch: { "stepStarted" : { $lte: currentTime }, status : "incomplete"}}}},
            {status: { $ne: "failed"}}
          ]},

          { $and: [
            {orderBody[currentStep - 1]: {$elemMatch: { "stepStarted" : { $lte: currentTime }, status : "incomplete"}}},
            {status: { $ne: "failed"}}
          ]},

Any assistance on this would be greatly appreciated.

Tushar Gupta - curioustushar · Accepted Answer · 2021-03-31 18:33:51Z

Demo - https://mongoplayground.net/p/d2ew5peV-z-

Use $project to extract exact array element pipeline you want from orderBody. Using $arrayElemAt.

$subtract currentStep value 1 to get the correct index ($toInt)

After that run your $match query on the document.

db.collection.aggregate({
  $project: {
    orderBody: {
      "$arrayElemAt": [ "$orderBody", { $subtract: [ { $toInt: "$currentStep" }, 1 ] } ]
    }
  }
},
{
  $match: {
    "orderBody.stepStarted": { $gte: NumberLong(1217207419302) },
    "orderBody.status": "incomplete"
  }
})

Note- add details you want to project in $project pipeline.

Update

Demo - https://mongoplayground.net/p/E8Wo_YfFltq

Use $addFields

db.collection.aggregate({
  $addFields: {
    currentOrderBody: { $arrayElemAt: [ "$orderBody", { $subtract: [ {  $toInt: "$currentStep" }, 1 ] } ] }
  }
},
{
  $match: {
    "currentOrderBody.stepStarted": { $gte: NumberLong(1217207419302) },
    "currentOrderBody.status": "incomplete"
  }
})

Collectives™ on Stack Overflow

MongoDB Query array based on value of another field

1 Answer 1

Update

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

Comments

Your Answer

Sign up or log in

Post as a guest

Related