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

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: [{
         archived: true,
         labelSelected: [],
         title: "mycard",
         description: "",
         attachments: [],
         comments: []
       }]
     },
     {
       archived: false,
       _id: ObjectId("6175f8c69c03810ea8e7b324"),
       cards: [{
         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: [{
         archived: false,
         labelSelected: [],
         title: "mycard",
         description: "",
         attachments: [],
         comments: []
       }]
     },
   ]
  }
]

CodePudding user response：

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"
      },
      
    }
  }
])

CodePudding user response：

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

Test code here

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"]}]}]}}}}}])