Query deep level nested documents with top level document id

user_6903497 0
Viewed 88

I've three levels of collections i.e., user, departments and projects. Here is how it is structured, each user will have more than one department and each department will have more than one project.

user -> departments -> projects

I've gone through other similar mongodb queries but none of them are solving my specific issue.

Here is how my whole data structure look like

{
"_id": "5a2ca2227c42ad67682731d4", // this is user id
"name": "XYZ Bank",
createdAt: "2021-04-08T04:05:30.340Z",
updatedAt: "2021-04-08T04:05:30.340Z",
"departments": [
    {
        "_id": "5a2ca2227c42ad6768271232",
        "name": "Digital",
        "userId": "5a2ca2227c42ad67682731d4",
        createdAt: "2021-04-08T04:05:30.340Z",
        updatedAt: "2021-04-08T04:05:30.340Z",
        "projects": [
            {
                "_id": "5a2ca2227c42ad6768274343",
                "name": "ABC",
                "departmentId": "5a2ca2227c42ad6768271232",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            },
            {
                "_id": "5a2ca2227c42ad6768274344",
                "name": "XYZ",
                "departmentId": "5a2ca2227c42ad6768271232",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            },
            {
                "_id": "5a2ca2227c42ad6768274345",
                "name": "BCA",
                "departmentId": "5a2ca2227c42ad6768271232",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            }
        ]
    },
    {
        "_id": "5a2ca2227c42ad6768271233",
        "name": "Finance",
        "userId": "5a2ca2227c42ad67682731d4",
        createdAt: "2021-04-08T04:05:30.340Z",
        updatedAt: "2021-04-08T04:05:30.340Z",
        "projects": [
            {
                "_id": "5a2ca2227c42ad6768274346",
                "name": "DSE",
                "departmentId": "5a2ca2227c42ad6768271233",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            },
            {
                "_id": "5a2ca2227c42ad6768274347",
                "name": "ADB",
                "departmentId": "5a2ca2227c42ad6768271233",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            },
            {
                "_id": "5a2ca2227c42ad6768274348",
                "name": "QAW",
                "departmentId": "5a2ca2227c42ad6768271233",
                createdAt: "2021-04-08T04:05:30.340Z",
                updatedAt: "2021-04-08T04:05:30.340Z",
            }
        ]
    }
]
}

The scenario which I'm struggling to solve is that I have to show recently added projects based on user id but since user doesn't have direct relation with projects I'm struggling to find a way to query recently added top 3 projects based on user id i.e., "_id": "5a2ca2227c42ad67682731d4"

What I generally do to query direct relations is

export async function getProjectsByDepartmentId (
  req: Request,
  res: Response
): Promise<any> {
  try {
    const query = {
      departmentId: req.query.departmentId,
    };
    const aggregators = [];
    aggregators.push({
      $facet: {
        data: [
          { $match: query },
          { $sort: { _id: -1 } },
          { $limit: req.query.limit },
        ],
        total: [{ $count: "count" }],
      },
    });

    const projects = await Project.aggregate(aggregators);
    return res.status(200).send(projects);
  } catch (err) {
    return res.status(500).send(err || err.message);
  }
}

The output that I'm expecting is

{
    "_id": "5a2ca2227c42ad67682731d4", // this is user id
    "name": "XYZ Bank",
    createdAt: "2021-04-08T04:05:30.340Z",
    updatedAt: "2021-04-08T04:05:30.340Z",
    "projects": [
                {
                    "_id": "5a2ca2227c42ad6768274343",
                    "name": "ABC",
                    "departmentId": "5a2ca2227c42ad6768271232",
                    createdAt: "2021-04-08T04:05:30.340Z",
                    updatedAt: "2021-04-08T04:05:30.340Z",
                },
                {
                    "_id": "5a2ca2227c42ad6768274344",
                    "name": "XYZ",
                    "departmentId": "5a2ca2227c42ad6768271232",
                    createdAt: "2021-04-08T04:05:30.340Z",
                    updatedAt: "2021-04-08T04:05:30.340Z",
                },
                {
                    "_id": "5a2ca2227c42ad6768274345",
                    "name": "BCA",
                    "departmentId": "5a2ca2227c42ad6768271232",
                    createdAt: "2021-04-08T04:05:30.340Z",
                    updatedAt: "2021-04-08T04:05:30.340Z",
                }
            ]
    ]
}

But have no idea how to query projects based on userId which has no direct relation. How can I achieve this? Appreciate your suggestions

mongoose node.js mongodb document
1 Answers

You will have wrap it _id with ObjectId() and createdAt with ISODate(''). I don't know why it is not working in mongoplayground

db.collection.aggregate([
  {
    $match: {
      _id: "5a2ca2227c42ad67682731d4"
    }
  },
  {
    $unwind: "$departments"
  },
  {
    $unwind: "$departments.projects"
  },
  {
    $match: {
      "departments.projects.createdAt": {
        $gte: "2021-04-09T00:00:00Z"
      }
    }
  }
])

MongoPlayground: https://mongoplayground.net/p/Qll_t6RBltL

user_108429000
Related