'How can I include an empty array after using $match in mongodb aggregation

I am trying to fetch boards.lists that have an empty cards array or some cards that are not archived, but using $match with $or operators not work. How can I solve this problem??

The sample document is like:

{
_id: ObjectId("616bcd746bfed71fdcf892c3"),
userId: ObjectId("611cb3a14f142d5d94daa395"),
name: "myworkspace",
description: "",
boards: [
 {
    _id: ObjectId("6175f8c69c03810ea8e7b31e"),
    name: 'myboard',
    color: '',
    labels: [
      { color: 'blue', title: 'project1' }
    ],
    lists: [
     {
       archived: true,
       _id: ObjectId("6175f8c69c03810ea8e7b321"),
       cards: []
     },
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b322"),
       cards: []
     },
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b323"),
       cards: [{
         _id: ObjectId("61721cbc3092d32970cf2fed")
         archived: true,
         labelSelected: [],
         title: "mycard",
         description: "",
         attachments: [],
         comments: []
       }]
     },
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b324"),
       cards: [{
         _id: ObjectId("6175f8d09c03810ea8e7b32d")
         archived: false,
         labelSelected: [],
         title: "mycard",
         description: "",
         attachments: [],
         comments: []
       }]
     },
   ]
  }
 ]
}

What I did:

 docs = await WorkspaceModel.aggregate([
      { $match: { _id: mongoose.Types.ObjectId(workspaceId) } },
      { $unwind: "$boards" },
      { $match: { "boards._id": mongoose.Types.ObjectId(boardId) } },
      { $unwind: "$boards.lists" },
      { $match: { "boards.lists.archived": false } },
      {
        $unwind: {
          path: "$boards.lists.cards",
          preserveNullAndEmptyArrays: true,
        },
      },
      {  
        $match: {
          $or: [
             { "boards.lists.cards": { $exists: true, $size: 0 } },
             { "boards.lists.cards.archived": false }
           ]
         },
       },
      {
        $group: {
          _id: "$boards._id",
          name: { $first: "$boards.name" },
          color: { $first: "$boards.color" },
          labels: { $first: "$boards.labels" },
          lists: { $addToSet: "$boards.lists" },
        },
      },
    ]).exec();

What I got: [], before the $match statement, it just shows all the lists that are not archived.

What I expect:

[
  {
    _id: ObjectId("6175f8c69c03810ea8e7b31e"),
    name: 'myboard',
    color: '',
    labels: [
      { color: 'blue', title: 'project1' }
    ],
    lists: [
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b322"),
       cards: []
     },
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b324"),
       cards: [{
         _id: ObjectId("6175f8d09c03810ea8e7b32d")
         archived: false,
         labelSelected: [],
         title: "mycard",
         description: "",
         attachments: [],
         comments: []
       }]
     },
   ]
  }
]
mongodbmongooseaggregation-framework


Solution 1:[1]

Query

  • unwind boards
  • from lists keep only that have
    (and (= archived false) (or empty_cards cards_contains_not_archived_card)
  • if you want to filter out the empty lists also you can add a match
    {"$match" : {"lists": {"$ne" : [] }}}
  • in your expected output you dont have an _id to know to which document each list belong, in the bellow query the same happens

PlayMongo

aggregate(
[{"$unwind": {"path": "$boards"}},
 {"$replaceRoot": {"newRoot": "$boards"}},
 {"$set": 
   {"lists": 
     {"$filter": 
       {"input": "$lists",
        "cond": 
        {"$and": 
          [{"$eq": ["$$this.archived", false]},
           {"$or": 
             [{"$eq": ["$$this.cards", []]},
             {"$in": [false, "$$this.cards.archived"]}]}]}}}}}])

Solution 2:[2]

the problem is from your match according to your sample data I create aggregate like this just with match and group if you need you can change or add some pipelines

https://mongoplayground.net/p/SWr_q-bI9A5

db.collection.aggregate([
  {
    "$unwind": "$lists"
  },
  {
    $match: {
      $or: [
        {
          "lists.cards": []
        },
        {
          "lists.cards": {
            $elemMatch: {
              "archived": false
            }
          }
        }
      ]
    }
  },
  {
    "$group": {
      "_id": "_id",
      "lists": {
        "$addToSet": "$lists"
      },
      "name": {
        "$first": "$name"
      },
      "lables": {
        "$first": "$labels"
      },
      "color": {
        "$first": "$color"
      },
      
    }
  }
])

Solution 3:[3]

this is my solution:

  1. $filter non archived list first and use as the input of $map
  2. $filter non archived or empty cards and map the result to each list
  3. it will show the non archived lists without cards or with non archived cards
 docs = await WorkspaceModel.aggregate([
      { $match: { _id: mongoose.Types.ObjectId(workspaceId) } },
      { $unwind: "$boards" },
      { $match: { "boards._id": mongoose.Types.ObjectId(boardId) } },
      { $replaceRoot: { newRoot: "$boards" } },
      {
        $set: {
          lists: {
            $map: {
              input: {
                $filter: {
                  input: "$lists",
                  as: "list",
                  cond: {
                    $eq: ["$$list.archived", false],
                  },
                },
              },
              as: "filteredList",
              in: {
                title: "$$filteredList.title",
                cards: {
                  $filter: {
                    input: "$$filteredList.cards",
                    as: "card",
                    cond: {
                      $or: [
                        { $eq: ["$$card", []] },
                        { $eq: ["$$card.archived", false] },
                      ],
                    },
                  },
                },
              },
            },
          },
        },
      },
    ]).exec();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 mohammad Naimi
Solution 3 Blake Sim

Related Questions