Find in nested array with compare on last field-CodePudding

I have a collection with documents like this one:

{
  f1: {
   firstArray: [
     {
      secondArray: [{status: "foo1"}, {status: "foo2"}, {status: "foo3"}]
     }
   ]
  }
}

My expected result includes documents that have at least one item in firstArray, which is last object status on the secondArray is included in an input array of values (eg. ["foo3"]).

I don't must use aggregate.

I tried:

    {
        "f1.firstArray": {
          $elemMatch: {
            "secondArray.status": {
              $in: ["foo3"],
            },
            otherField: "bar",
          },
        },
      }

CodePudding user response：

You can use an aggregation pipeline with $match and $filter, to keep only documents that their size of matching last items are greater than zero:

db.collection.aggregate([
  {$match: {
      $expr: {
        $gt: [
          {$size: {
              $filter: {
                input: "$f1.firstArray",
                cond: {$in: [{$last: "$$this.secondArray.status"}, ["foo3"]]}
              }
            }
          },
          0
        ]
      }
    }
  }
])

See how it works on the playground example

If you know that the secondArray have always 3 items you can do:

db.collection.find({
  "f1.firstArray": {
    $elemMatch: {
      "secondArray.2.status": {
        $in: ["foo3"]
      }
    }
  }
})

But otherwise I don't think you can check only the last item without an aggregaation. The idea is that a regular find allows you to write a query that do not use values that are specific for each document. If the size of the array can be different on each document or even on different items on the same document, you need to use an aggregation pipeline