How to get not mapped values using multiple collection

Question

table1

{
    "_id" : "123",
    "name" : "A",
},
{
    "_id" : "234"
    "name" : "B",
}

table2

{
    "_id" : "432",
    "language" : "Hindi"
},
{
    "_id" : "345345",
    "language" : "Hindi"
}

table3

{
    "_id" : "3498",
    "film" : {
        "kannada" : [
            "200" 
        ],
    }
}

Step 1

I have to take _id from table1 check with table2 then take language from table2

Step 2

we have to check this language mapped to table3 collection or not ?.

Expected Output:

["100"]

Why 100... why not 100 and 200? – Ashh Jan 10 '19 at 05:40 — Ashh, Jan 10 '19 at 05:40

mickl · Accepted Answer · 2019-01-10T17:20:07.590

You can use below aggregation:

db.UserDetails.aggregate(
    {
    $lookup: {
        from: "UserProducts",
        localField: "UID",
        foreignField: "UID",
        as: "userProduct"
        }
    },
    { $unwind: "$userProduct" },
    {
        "$project": { "_id" : 0, "userProduct.UPID" : 1 }
    },
    {
        $group: {
            _id: null,
            userProductUPIDs: { $addToSet: "$userProduct.UPID" }
        }
    },
    {
        $lookup: {
            from: "Groups",
            pipeline: [
                { $unwind: "$members.regularStudent" },
                { $project: { _id: 0, value: "$members.regularStudent" } }
            ],
            as: "UPID"
        }
    },
    {
        $addFields: {
            UPID: {
                $map: {
                    input: "$UPID",
                    as: "x",
                    in: "$$x.value"
                }
            }
        }
    },
    {
        $project: {
            result: {
                $setDifference: [ "$userProductUPIDs", "$UPID" ]
            }
        }
    }
)

Basically the aim is to get single document with two arrays to perform $setDifference. You already have the right part (just need to add $group) and you need $lookup with custom pipeline to get all the data from Groups into one collection. Outputs:

{ "_id" : null, "result" : [ "100" ] }

EDIT: to run $lookup with custom pipeline you need MongoDB 3.6 or newer. Alternatively you have to run two aggregations and then compare both results in your application logic:

var queryResult = db.UserDetails.aggregate(
    {
    $lookup: {
        from: "UserProducts",
        localField: "UID",
        foreignField: "UID",
        as: "userProduct"
        }
    },
    { $unwind: "$userProduct" },
    {
        "$project": { "_id" : 0, "userProduct.UPID" : 1 }
    },
    {
        $group: {
            _id: null,
            userProductUPIDs: { $addToSet: "$userProduct.UPID" }
        }
    }
) // returns [ "100", "200" ]

let userProductUPIDs = queryResult.toArray()[0].userProductUPIDs;

db.Groups.aggregate([
    {
        $unwind: "$members.regularStudent"
    },
    {
        $group: {
            _id: null,
            UPIDs: { $addToSet: "$members.regularStudent" }
        }
    },
    {
        $project: {
            members: {
                $setDifference: [ userProductUPIDs , "$UPIDs" ]
            },
            _id : 0
        }
    }
])

@Prasanna you need at least `3.6` since this `$lookup` with custom pipeline is the only way to get the data from other collection with custom condition (by default you have only equality). Can you update your DB ? — mickl, Jan 10 '19 at 06:22
@Prasanna unfortunately I don't see any fallback for `3.4.5`, the only thing that comes to my mind is that you need two database queries. So you can either convince your boss to upgrade the DB or I can show you how to do that using two DB calls — mickl, Jan 10 '19 at 06:36
@Prasanna my code is returning two arrays and you need to calculate the difference between first one and the second one — mickl, Jan 10 '19 at 08:18
@Prasanna answered here: https://stackoverflow.com/a/54130629/6238977 hope it's more clear now — mickl, Jan 10 '19 at 14:17

How to get not mapped values using multiple collection

1 Answers1