Request to check all array elements-CodePudding

I have a problem with MongoDB syntax.
I have two documents:
alley(the "tree" field is the ID of the tree):

 {
        "_id": {"$oid": "62572d82cc40164fef7f1a56"},
        "name": "good alley",
        "tree": [
          {"$oid": "626976eb4b93122bc617d701"},
          {"$oid": "626976eb4b93122bc617d702"}
        ]
      },

.......
tree:

{
            "_id": {"$oid": "626976eb4b93122bc617d701"},
            "dateInstall": {"$date": "2021-02-27T00:00:00.000Z"},
            "species": [
              {"$oid": "62585a63edfc726a4ff24fb8"}
            ]
          },

.......

I need to write a query "an alley where trees were not planted last year"
My Code

db.alley.aggregate([
    {
        $lookup: {
            from: "tree",
            localField: "tree",
            foreignField: "_id",
            as: "tree"
        }
    },
    {
        $match: {{$not:{$and:[
                    {"tree.dateInstall": {$gt: new ISODate("2020-12-31")}},
                    {"tree.dateInstall": {$lt: new ISODate("2022-01-01")}}
                    ]
            }}}
    }
]);

CodePudding user response：

You should first $unwind trees in alleys so you can properly $lookup the trees in tree collection. Use pipeline inside lookup to query only trees planted last year. Finally $group trees into alleys again and use $match to filter out those alleys without trees.

db.getCollection("alley").aggregate([
  {
    $unwind: "$tree",
  },
  {
    $lookup: {
        from: "tree",
        let: { tree: "$tree" },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: [ "$$tree", "$_id" ] },
                  { $gt: [ "$dateInstall", new ISODate("2020-12-31") ] },
                  { $lt: [ "$dateInstall", new ISODate("2022-01-01") ] },
                ]
              }
            }
          }
        ],
        localField: "tree",
        foreignField: "_id",
        as: "tree"
    }
  },
  {
    $group: {
      _id: { id: "$_id", name: "$name" },
      trees: { $addToSet: { $first: "$tree" } }
    }
  },
  {
    $match: {
      trees: { $size: 0 }
    }
  }
])