Search a nested document which is inside array of obejctId (ref)

Question

I have a parent collection schema of workspace like this:

[{
  "_id": {
    "$oid": "650152787b7376a6fc4b1f76"
  },
  "title": "Apps",
  "description": "Apps..vb",
  "properties": {
    "icon": "home",
    "background": "#000000"
  },
  "workbooks": [
    {
      "$oid": "653217e3133321397fcf945c"
    },
    {
      "$oid": "6511674ed0a5e9db5823051b"
    },
    {
      "$oid": "6512c86efeb9749a302815d1"
    },
    {
      "$oid": "651e51b7b67a8ed5db942f82"
    },
    {
      "$oid": "6527cfaebcab179a6a1126be"
    },
    {
      "$oid": "6528e22d2769a36176438a7b"
    },
    {
      "$oid": "652cf7347abc3851521c3a17"
    },
    {
      "$oid": "65324ea46f67806031a9925c"
    },
    {
      "$oid": "65324ea96f67806031a9928d"
    }
  ],
  "isActive": true,
  "createdBy": "rdhoundiyal",
  "updatedBy": "rdhoundiyal",
  "primaryUser": [
    {
      "$oid": "65014e0fc2286b55e79a070d"
    },
    {
      "$oid": "6528e01b2769a36176438730"
    },
    {
      "$oid": "65014c6303b5bf048f627b72"
    }
  ],
  "otherUsers": [
    {
      "$oid": "650167123cc7410860126076"
    }
  ],
  "isDeleted": false,
  "createdAt": {
    "$date": "2023-09-13T06:11:04.806Z"
  },
  "updatedAt": {
    "$date": "2023-10-20T09:55:53.372Z"
  }
}]

and child document workbooks like this:

 [{
  "_id": {
    "$oid": "653217e3133321397fcf945c"
  },
  "title": "wb_008_copy_copy_copy_copy",
  "description": "",
  "properties": {
    "icon": "windows"
  },
  "primaryUser": [
    {
      "$oid": "652fae2f707c8e77d946bf5f"
    }
  ],
  "otherUser": [],
  "worksheets": [
    {
      "$oid": "653217e3133321397fcf945b"
    }
  ],
  "workspace": {
    "$oid": "652fb59b707c8e77d946cca3"
  },
  "isActive": true,
  "createdAt": {
    "$date": "2023-10-20T06:02:11.511Z"
  },
  "updatedAt": {
    "$date": "2023-10-22T05:36:01.978Z"
  },
  "isArchived": true
},
{
  "_id": {
    "$oid": "6511674ed0a5e9db5823051b"
  },
  "title": "wb_008_copy_",
  "description": "",
  "properties": {
    "icon": "windows"
  },
  "primaryUser": [
    {
      "$oid": "652fae2f707c8e77d946bf5f"
    }
  ],
  "otherUser": [],
  "worksheets": [
    {
      "$oid": "653217e3133321397fcf945b"
    }
  ],
  "workspace": {
    "$oid": "652fb59b707c8e77d946cca3"
  },
  "isActive": true,
  "createdAt": {
    "$date": "2023-10-20T06:02:11.511Z"
  },
  "updatedAt": {
    "$date": "2023-10-22T05:36:01.978Z"
  },
  "isArchived": false
}]

I want a query in which whenever I search for any workspace(parent-id), I don't want the workbooks which have isArchived=true. That means in the result array of workspace->workbooks, that particular workbookId should not be there. Not sure how to acheive this nested search. Need help in this.

Answer 1

You can filter them and replace the field with $set combined with using $filter like so:

db.workspace.aggregate([
  {
    $match: {
      _id: "650152787b7376a6fc4b1f76"
    }
  },
  {
    $lookup: {
      "from": "workbooks",
      "localField": "workbooks",
      "foreignField": "_id",
      "as": "workbooks"
    }
  },
  {
    $set: {
      workbooks: {
        $filter: {
          input: "$workbooks",
          as: "wb",
          cond: {
            $ne: [
              "$$wb.isArchived",
              true
            ]
          }
        }
      }
    }
  }
])

See here for a working example.

$set is useful here because:

$set outputs documents that contain all existing fields from the input documents and newly added fields. The $set stage is an alias for $addFields. Both stages are equivalent to a $project stage that explicitly specifies all existing fields in the input documents and adds the new fields.

Edit:

If you wanted to use mongoose then you could do this with populate:

const workspaces = await WorkspaceModel.find()
.populate({
   path: 'workbooks', 
   model: WorkbookModel, 
   match: { isArchived: {$ne: true} }
});

If you only want the _id returned in the workbooks array you can do a select like so:

const workspaces = await WorkspaceModel.find()
.populate({
   path: 'workbooks', 
   model: WorkbookModel, 
   match: { isArchived: {$ne: true} }, 
   select: '_id'
});