Query filter in subarray

Working with Data
#1

I would like to get documents according to 2 conditions (see below a sample of documents) :

  • Documents must contain at least one event with category “Temperatures” and event “technicalFailure”.
  • The last event in the event list must be of category “Temperatures” and have the event “technicalFailure”.

I am trying to use the operator $filterlike 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 ?

Thanks !

A 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: "Temperatures",
            event: "auto",
            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"
          }
        ]
      }
    ]
  }
]
#2

Hello, @Theo_Bollecker ! Welcome to the MongoDB community :wave:

You can do it like this:

db.test.aggregate([
  // initial document filter
  {
    $match: {
      "transports.events.category": "Temperatures",
      "transports.events.event": "technicalFailure"
    },
  },
  // add temporary shortcut field for last event object
  {
    $addFields: {
      tmp: {
        $firstN: {
          n: 1,
          input: '$transports'
        }
      }
    }
  },
  {
    $unwind: '$tmp'
  },
  {
    $addFields: {
      tmp: {
        $lastN: {
          n: 1,
          input: '$tmp.events'
        }
      }
    }
  },
  {
    $unwind: '$tmp'
  },
  // query your event
  {
    $match: {
      'tmp.category': 'Temperatures',
      'tmp.event': 'technicalFailure'
    }
  },
  // clean up
  {
    $project: {
      tmp: false,
    }
  }
]);
1 Like