mongodb - Find count of values in other collection

Question

I want to find how many "taskId" of below collectionOne is present in collectionTwo. Here, "taskId" in below collectionOne is embedded inside a array in a document.

Also, Let me know the different approaches to do this (if possible).

db.collectionOne.find({"sId":"DCNrnPeKFrBv" }).pretty()
{
        "_id" : "sX8o7mJhebs",
        "sId" : "DCNrnPeKFrBv",
        "data" : [
                {
                        "oId" : "7SycYQ",
                        "taskId" : 146108906
                },
                {
                        "oId" : "7SycYQ",
                        "taskId" : 14623846
                },
                {
                        "oId" : "fANQ",
                        "taskId" : 1461982
                },
                {
                        "oId" : "fAeNQ",
                        "taskId" : 131732
                },
                {
                        "oId" : "t6AF5yn",
                        "taskId" : 197681
                }
        ]
}



> db.collectionTwo.find().pretty().limit(2)
{
        "_id" : 146108906,
        "oId" : "7SycYQ",
        "name" : "ABC"
}
{
        "_id" : 1461982,
        "oId" : "fANQ",
        "name" : "XYZ"
}

In collectionTwo "_id" is equivalent to "taskId" of collectionOne.

Added sample documents of collectionTwo. In collectionTwo "_id" is equivalent to "taskId" of collectionOne. — Uday Singh, Aug 19 '16 at 07:49

chridam · Accepted Answer · 2022-04-06T18:51:36.393

6

Using the $lookup operator to do a left join on collectionTwo, you can get the counts as follows:

db.collectionOne.aggregate([
    { $match: { sId: "DCNrnPeKFrBv" }},
    { $lookup: {
        from: "collectionTwo",
        localField: "data.taskId",
        foreignField: "_id",
        as: "tasksCount"
    } },
    { $addFields: { 
        tasksCount: { $size: "$tasksCount" }
    } }
])

or if using older MongoDB server versions (below 3.2):

db.collectionOne.aggregate([
    { $unwind: "$data" },
    { $lookup: {
        from: "collectionTwo",
        localField: "data.taskId",
        foreignField: "_id",
        as: "tasks"
    } },
    { $unwind: "$tasks" },
    { $group: {
        _id: "$tasks._id",
        count: { "$sum": 1 }
    } },
    { $group: {
        _id: null,
        tasksCount: { "$sum": "$count" }
    } }
])

--EDIT--

An alternative is to get a list of all the distinct taskIds in collectionOne, and use that list as count query on collectionTwo e.g.

var taskIds = db.collectionOne.distinct("data.taskId");
var tasksCount = db.collectionTwo.count({ "_id": { "$in": taskIds } });
printjson(taskIds);
printjson(tasksCount);

edited Apr 06 '22 at 18:51

answered Aug 19 '16 at 08:10

chridam

100,957
23
236
235

Can we change its output to {"count" : 2} -- Just the count , instead of two documents as output. { "_id" : 1461982, "count" : 1 } { "_id" : 146108906, "count" : 1 } – Uday Singh Aug 19 '16 at 08:46
Hi, I need total count of all taskIds present in collectionOne. (This is the requirement). In above scenario it is two, so i need output as {"count" : 2} – Uday Singh Aug 19 '16 at 09:17
Typo on above comment. My Bad. I need total count of all taskIds present in collectionTwo. (This is the requirement). In above scenario it is only two taskIds of collectionOne is present in collectionTwo, so i need output as {"count" : 2} – Uday Singh Aug 19 '16 at 09:28
Yes, the new answer is fine. Can you please give me a solution for MongoDB version below 3.2. – Uday Singh Aug 19 '16 at 09:32
@user3802384 Updated with an alternative – chridam Aug 19 '16 at 09:42
1

Awesome.. Thanks a lot for the solution & quick responce. :) – Uday Singh Aug 19 '16 at 09:51

mongodb - Find count of values in other collection

1 Answers1