Question 239003 in Mongodb Question posted in
The official documentation can be found here.

Query MongoDB with multiples filters in subarrays

I would like to all documents which the last event of category Temperatures is technicalFailure even if there are other events after this one.

To give more context, the query is the result of a http request with query parameters. If receives technicalFailure, we should return all documents which the last event of category Temperatures is technicalFailure whatever the next events of different category (for instance, category "Payment" with event "failed")

Therefore, another event with different category could be the last event in events array.

I am trying to use the operator $filter like below, but it is not working expectedly

{
  $and: [
    {
      "transports.events.category": "Temperatures",
      "transports.events.event": "technicalFailure"
    },
    {
      $expr: {
        $eq: [
          {
            $arrayElemAt: [
              {
                $filter: {
                  input: "$transports[0].events",
                  cond: {
                    $and: [
                      { $eq: ["$$this.category", "Temperatures"] },
                      { $eq: ["$$this.event", "technicalFailure"] }
                    ]
                  }
                }
              },
              -1
            ]
          },
          {
            category: "Temperatures",
            event: "technicalFailure"
          }
        ]
      }
    }
  ]
}

Anyone can help for this query ?

The sample of documents :

[
  {
    transports: [
      {
        _id: "123",
        events: [
          {
            category: "Payment",
            event: "ok",
            date: "2023-02-21T16:32:07.740Z"
          },
          {
            category: "Temperatures",
            event: "technicalFailure",
            date: "2023-02-21T16:36:07.740Z"
          },
          {
            category: "Payment",
            event: "failed",
            date: "2023-02-21T16:55:07.740Z"
          }
        ]
      }
    ]
  },
  {
    transports: [
      {
        _id: "456",
        events: [
          {
            category: "Payment",
            event: "ok",
            date: "2023-02-21T16:28:07.740Z"
          },
          {
            category: "Temperatures",
            event: "auto",
            date: "2023-02-21T16:29:07.740Z"
          },
          {
            category: "Payment",
            event: "failed",
            date: "2023-02-21T17:01:07.740Z"
          }
        ]
      }
    ]
  },
  {
    transports: [
      {
        _id: "127",
        events: [
          {
            category: "Payment",
            event: "ok",
            date: "2023-02-21T16:29:07.740Z"
          },
          {
            category: "Temperatures",
            event: "auto",
            date: "2023-02-21T17:18:07.740Z"
          },
          {
            category: "Temperatures",
            event: "technicalFailure",
            date: "2023-02-21T18:53:07.740Z"
          }
        ]
      }
    ]
  }
]

The expected result :

[
  {
    transports: [
      {
        _id: "123",
        events: [
          {
            category: "Payment",
            event: "ok",
            date: "2023-02-21T16:32:07.740Z"
          },
          {
            category: "Temperatures",
            event: "technicalFailure",
            date: "2023-02-21T16:36:07.740Z"
          },
          {
            category: "Payment",
            event: "failed",
            date: "2023-02-21T16:55:07.740Z"
          }
        ]
      }
    ]
  },
  {
    transports: [
      {
        _id: "127",
        events: [
          {
            category: "Payment",
            event: "ok",
            date: "2023-02-21T16:29:07.740Z"
          },
          {
            category: "Temperatures",
            event: "auto",
            date: "2023-02-21T17:18:07.740Z"
          },
          {
            category: "Temperatures",
            event: "technicalFailure",
            date: "2023-02-21T18:53:07.740Z"
          }
        ]
      }
    ]
  }
]
Tags:

2

Answers


  1. 0

    If I understand correctly, to fulfill your 2nd condition that last element is of category "Temperatures" and have the event "technicalFailure", the 1st condition must also be fulfilled at the same time. So, it boils to checking the 2nd condition only and we only need to check last element in array. We can then proceed to extract the last element with $last and chain it up with $anyElementTrue for array processing.

    db.collection.aggregate([
  {
    "$set": {
      "lastEvent": {
        "$map": {
          "input": "$transports",
          "as": "t",
          "in": {
            $last: "$$t.events"
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        "$anyElementTrue": {
          "$map": {
            "input": "$lastEvent",
            "as": "le",
            "in": {
              $and: [
                {
                  $eq: [
                    "$$le.category",
                    "Temperatures"
                  ]
                },
                {
                  $eq: [
                    "$$le.event",
                    "technicalFailure"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $unset: "lastEvent"
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    If I understand your updated requirements, here’s one way you could do it. Comments are in the aggregation pipeline.

    db.collection.aggregate([
  { // eliminate docs without possibility of match
    "$match": {
      "transports.events": {
        "$elemMatch": {
          "category": "Temperatures",
          "event": "technicalFailure"
        }
      }
    }
  },
  { // check final condition of remaining docs
    "$match": {
      "$expr": {
        "$reduce": {
          "input": "$transports",
          "initialValue": false,
          "in": {
            "$let": {
              "vars": {
                // set the last "Temperatures" event
                "lastEvent": {
                  "$last": {
                    "$filter": {
                      "input": "$$this.events",
                      "as": "event",
                      "cond": {
                        "$eq": ["$$event.category", "Temperatures"]
                      }
                    }
                  }
                }
              },
              "in": {
                "$or": [
                  "$$value",
                  {
                    "$eq": ["$$lastEvent.event","technicalFailure"]
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

    Try it on mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search