How do I re-group my unwind items back in mongodb?

Question

I'm building a kanban board and i need to project my whole kanban document as a minified document.

It is a kanban object with columns as embeded documents, and cards as subdocument of every column.

I have this dataset:

{
  "_id": {
    "$oid": "609b2ab4c60588f6b0579259"
  },
  "title": "Entregas",
  "ownerId": "6026af58714618ac77356d2c",
  "columns": [
    {
      "headerText": "Backlog",
      "keyField": "Backlog",
      "cards": []
    },
    {
      "headerText": "Column 1",
      "keyField": "Column 1",
      "cards": [
        {
          "design": "default",
          "rank": 0,
          "labels": [
            {
              "color": "#61bd4f",
              "text": "",
              "active": false
            },
            {
              "color": "#eb5a46",
              "text": "",
              "active": true
            },
            {
              "color": "#f5dd29",
              "text": "",
              "active": false
            },
            {
              "color": "#ff9f1a",
              "text": "",
              "active": true
            },
            {
              "color": "#c377e0",
              "text": "",
              "active": true
            },
            {
              "color": "#0079bf",
              "text": "",
              "active": false
            }
          ],
          "description": "Ah valeyhjs",
          "_id": {
            "$oid": "609d3a63fc07b66ba8168215"
          },
          "title": "test2",
          "parentColumn": "Column 1",
          "creationDate": {
            "$date": "2021-05-13T14:40:35.497Z"
          },
          "updatedAt": {
            "$date": "2021-05-13T14:43:49.287Z"
          }
        },
        {
          "design": "default",
          "rank": 0,
          "labels": [
            {
              "color": "#61bd4f",
              "text": "",
              "active": false
            },
            {
              "color": "#eb5a46",
              "text": "",
              "active": false
            },
            {
              "color": "#f5dd29",
              "text": "",
              "active": false
            },
            {
              "color": "#ff9f1a",
              "text": "",
              "active": false
            },
            {
              "color": "#c377e0",
              "text": "",
              "active": false
            },
            {
              "color": "#0079bf",
              "text": "",
              "active": false
            }
          ],
          "description": "",
          "_id": {
            "$oid": "609e6ee8865e834d14bd1ff5"
          },
          "title": "xd",
          "parentColumn": "Column 1",
          "creationDate": {
            "$date": "2021-05-14T12:36:56.473Z"
          },
          "updatedAt": {
            "$date": "2021-05-14T12:36:56.473Z"
          }
        }
      ]
    },
    {
      "headerText": "Column 2",
      "keyField": "Column 2",
      "cards": [
        {
          "design": "default",
          "rank": 0,
          "labels": [
            {
              "color": "#61bd4f",
              "text": "",
              "active": false
            },
            {
              "color": "#eb5a46",
              "text": "",
              "active": false
            },
            {
              "color": "#f5dd29",
              "text": "",
              "active": false
            },
            {
              "color": "#ff9f1a",
              "text": "",
              "active": false
            },
            {
              "color": "#c377e0",
              "text": "",
              "active": false
            },
            {
              "color": "#0079bf",
              "text": "",
              "active": false
            }
          ],
          "description": "",
          "_id": {
            "$oid": "609d3984fc07b66ba8168214"
          },
          "title": "test",
          "parentColumn": "Column 2",
          "creationDate": {
            "$date": "2021-05-13T14:36:52.899Z"
          },
          "updatedAt": {
            "$date": "2021-05-13T14:36:52.899Z"
          }
        }
      ]
    }
  ],
  "creationDate": {
    "$date": "2021-05-12T01:09:09.000Z"
  },
  "updatedAt": {
    "$date": "2021-05-18T11:52:45.925Z"
  },
  "__v": 52
}

And this is my aggregation

    [
    {
        '$match': {
            '_id': ObjectId('609b2ab4c60588f6b0579259')
        }
    }, {
        '$unwind': {
            'path': '$columns'
        }
    }, {
        '$unwind': {
            'path': '$columns.cards', 
            'preserveNullAndEmptyArrays': True
        }
    }, {
        '$project': {
            'title': 1, 
            'ownerId': 1, 
            'columns.headerText': 1, 
            'columns.keyField': 1, 
            'columns.cards.labels': 1, 
            'columns.cards.title': 1, 
            'columns.cards.parentColumn': 1, 
            'columns.cards._id': 1, 
            'columns.cards.attachments': {
                '$size': {
                    '$ifNull': [
                        '$columns.cards.attachments', []
                    ]
                }
            }, 
            'columns.cards.checkLists': {
                '$size': {
                    '$ifNull': [
                        '$columns.cards.checklLists', []
                    ]
                }
            }, 
            'columns.cards.activity': {
                '$size': {
                    '$ifNull': [
                        '$columns.cards.activity', []
                    ]
                }
            }, 
            'creationDate': 1, 
            'updatedAt': 1
        }
    }, {
        '$group': {
            '_id': '$_id', 
            'title': {
                '$first': '$title'
            }, 
            'ownerId': {
                '$first': '$ownerId'
            }, 
            'columns': {
                '$push': '$columns'
            }, 
            'creationDate': {
                '$first': '$creationDate'
            }, 
            'updatedAt': {
                '$first': '$updatedAt'
            }
        }
    }
]

I'm trying to get only the sizes of every array inside my Card sub documents. My trouble is that i'm getting my final document with the wrong cards. I need to group them back with their parent columns.

user_8987128 · Answer

You can try without $unwind using $map,

$map to iterate loop of columns array
$map to iterate loop of cards array
add your required fields and merge with current document using $mergeObjects
$mergeObjects to merge current document of columns and cards updated field

db.collection.aggregate([
  { "$match": { "_id": ObjectId("609b2ab4c60588f6b0579259") } },
  {
    $addFields: {
      columns: {
        $map: {
          input: "$columns",
          in: {
            $mergeObjects: [
              "$$this",
              {
                cards: {
                  $map: {
                    input: "$$this.cards",
                    in: {
                      $mergeObjects: [
                        "$$this",
                        {
                          attachments: {
                            $size: { $ifNull: ["$$this.attachments", []] }
                          }
                        },
                        {
                          checkLists: {
                            $size: { $ifNull: ["$$this.checkLists", []] }
                          }
                        },
                        {
                          activity: {
                            $size: { $ifNull: ["$$this.activity", []] }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

How do I re-group my unwind items back in mongodb?

1 Answers