Mongodb lookup for not equal fields

Question

I want to join two collections and find the documents where has one equal field and one unequal field! This is what I was tried, But not work

db.collectionOne.aggregate[
    {
        "$match": {
            "$and": [
                { "$text": { "$search": "this is my query" } },
                { "b": { "$eq": "60e849054d2f0d409041b6a2" } }
            ]
        }
    },
    { "$addFields": { "pID": { "$toString": "$_id" }, "score": { "$meta": "textScore" } } },
    {
        "$lookup": {
            "from": "collectionsTwo",
            "as": "collectionsTwoName",
            "pipeline": [{
                "$match": {
                    "$expr": {
                        "$and": [{
                            "$ne": ["$fieldOne", "60dd0f98d10f072e2a225502"] // This one is unqual field
                        }, { "$eq": ["$pID", "$fieldTwo"] }] // This one is equal field
                    }
                }
            }]
        }
    },
    { "$sort": { "score": -1 } },
    { "$limit": 1 }
])

score 0 · Answer 1 · answered Jul 10 '21 at 00:20

0

Fields in the source document, i.e. $pID are not available inside the lookup pipeline.

In order to reference those values, you would need to define a variable using let, such as:

{
        "$lookup": {
            "from": "collectionsTwo",
            "as": "collectionsTwoName",
            "let": { "srcpID":"$pID" },
            "pipeline": [{
                "$match": {
                    "$expr": {
                        "$and": [{
                            "$ne": ["$fieldOne", "60dd0f98d10f072e2a225502"] // This one is unqual field
                        }, { "$eq": ["$$srcpID", "$fieldTwo"] }] // This one is equal field
                    }
                }
            }]
        }
    },

See https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries

answered Jul 10 '21 at 00:20

Joe

25,000
3
22
44

HI joe thanks for the answer, Actualy `"$eq": ["$pID", "$fieldTwo"] }` works , `"$ne": ["$fieldOne", "60dd0f98d10f072e2a225502"]` not work – aidinMC Jul 10 '21 at 09:00
Should I add `$fieldOne` to `let` too? – aidinMC Jul 10 '21 at 09:02
If `fieldOne` belongs to the document from `collectionOne`, then yes. – Joe Jul 10 '21 at 09:03
Also note that if `fieldOne` contains an ObjectId, that `$ne `comparison will always match. – Joe Jul 10 '21 at 09:04
No its belongs to collectionTwo and hold String value – aidinMC Jul 10 '21 at 09:12
some sample documents would help make sense of this. – Joe Jul 10 '21 at 09:15

Mongodb lookup for not equal fields

1 Answers1