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 } ])

Joe · Accepted Answer · 2021-07-10 00:20:05Z

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

HI joe thanks for the answer, Actualy "$eq": ["$pID", "$fieldTwo"] } works , "$ne": ["$fieldOne", "60dd0f98d10f072e2a225502"] not work
If fieldOne belongs to the document from collectionOne, then yes.
Also note that if fieldOne contains an ObjectId, that $ne comparison will always match.

Collectives™ on Stack Overflow

Mongodb lookup for not equal fields

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related