CODEWITHSUNDEEP
mongodb
Dhar_
Dhar_

Reputation: 19

How can I query 2 collections to find where the _id is the same but x is different in MongoDB

I'm trying write a query in MongoDB where I can search through all of the records in both collections (both collections have the same amount of records and identical _id fields for equivalent records) and return a list where the _id is the same but the value for 'x' is different.

For example, if both collections have an _id = apples but the flavour value is different for each collection, then I want to return apples.

Upvotes: 0

Views: 464

Answers (1)

DayDreamer
DayDreamer

Reputation: 116

collection

/* colection 1*/

{"_id" : "orange"   ,"flavour" : "sour"},
{"_id" : "mango"    ,"flavour" : "sour"},
{"_id" : "apple"    ,"flavour" : "sweet"}

/*collection  2*/

{"_id" : "orange"   ,"flavour" : "sour"},
{"_id" : "mango"    ,"flavour" : "sweet"},
{"_id" : "apple"    ,"flavour" : "sour"}

Query1

db.collection1.aggregate([
    {
        $lookup: {
            from: 'collection2',
            let: { fid: '$_id', fflavour: '$flavour'}, // foreign
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ['$_id', '$$fid'] },
                                { $ne: ['$flavour', '$$fflavour'] }
                            ]
                 }}}
            ],
            as: 'foreign'
    }},
    {
        $match: {
            foreign: {$not: { $size: 0 }
    }}}
])

Query 2

db.collection1.aggregate([
    {
        $lookup: {
            from: 'collection2',
            localField: '_id',
            foreignField: '_id',
            as: 'foreign'
        }
    }, 
    {
        $match: {
            $expr: {
                $not: {
                    $in: ['$flavour','$foreign.flavour']
    }}}}
])

return same result, but I'm not sure both query are the best way

{
    "_id" : "apple",
    "flavour" : "sweet",
    "foreign" : [
        {"_id" : "apple","flavour" : "sour"}
    ]
},
{
    "_id" : "mango",
    "flavour" : "sour",
    "foreign" : [
        {"_id" : "mango","flavour" : "sweet"}
    ]
}

Upvotes: 1

Related Questions