MongoDB find all the object that contains an array which has consecutive matching value

user_6480803 0
Viewed 59

I have a MongoDB collection which looks like this:

{
  _id: "some-long-id-1"
  name: "John Doe",
  activities: [
    { name: "Lunch", status: "SCHEDULED" },
    { name: "Playing Football", status: "COMPLETED" },
    { name: "Workout", status: "COMPLETED" },
  ]
},
{
  _id: "some-long-id-2"
  name: "Jane Doe",
  activities: [
    { name: "Lunch", status: "COMPLETED" },
    { name: "Playing Football", status: "SCHEDULED" },
    { name: "Workout", status: "COMPLETED" },
  ]
}

I want to query all the objects which has activities which contains at least one consecutive COMPLETED status. For example, the query should return some-long-id-1 object as it has a consecutive COMPLETED status in the activities array. It shouldn't return some-long-id-2 as even though there are two arrays with COMPLETED status but they are not consecutive.

mongodb-query mongodb
2 Answers

Query

  • reduce array to a number (that counts the consecutive that we find)
    • 0 not found
    • 1 found one
    • 2 found 2
  • we need the reduce result to be = 2
  • if 2 => keep 2
    else if "completed" => inc the number
    else 0 (restart counting)

PlayMongo

aggregate(
[{"$match": 
    {"$expr": 
      {"$eq": 
        [{"$reduce": 
            {"input": "$activities",
              "initialValue": 0,
              "in": 
              {"$switch": 
                {"branches": 
                  [{"case": {"$eq": ["$$value", 2]}, "then": "$$value"},
                    {"case": {"$eq": ["$$this.status", "COMPLETED"]},
                      "then": {"$add": ["$$value", 1]}}],
                  "default": 0}}}}, 2]}}}])
user_48826920

You can use a $reduce to do this. Use a tuple of flag consecutiveFound to store if consecutive "COMPLETED" is found; and lastStatus to store the status of last element.

The initial value of the accumulator can be like this:

{
    "consecutiveFound": false,
    "lastStatus": ""
}

For the logic of $reduce, if consecutiveFound is true, we have found consecutive already so keeping "consecutiveFound": true. If "consecutiveFound": false, we just keep comparing lastStatus and current status to see whether they are both completed. We only update the accumulator value to "consecutiveFound": true when we found both are COMPLETED. Remember to update the current value to lastStatus in accumulator everytime.

{
    "$reduce": {
        "input": "$activities",
        "initialValue": {
        "consecutiveFound": false,
        lastStatus: ""
        },
        "in": {
        "$cond": {
            "if": {
            $or: [
                {
                $eq: [
                    "$$value.consecutiveFound",
                    true
                ]
                },
                {
                $and: [
                    {
                    $eq: [
                        "$$value.lastStatus",
                        "COMPLETED"
                    ]
                    },
                    {
                    $eq: [
                        "$$this.status",
                        "COMPLETED"
                    ]
                    }
                ]
                }
            ]
            },
            "then": {
                "consecutiveFound": true,
                lastStatus: "$$this.status"
            },
            "else": {
                "consecutiveFound": false,
                lastStatus: "$$this.status"
            }
        }
        }
    }
}

Here is the Mongo playground for your reference.

user_147326690
Related