CODEWITHSUNDEEP
node.jsmongodb
Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

Mongodb query multiple collection

I have two collection

user

[{
    "id":1,
    "name":"a1",
    "emailAddress":"[email protected]",
},
{
    "id":2,
    "name":"a2",
    "emailAddress":"[email protected]",
},
{
    "id":3,
    "name":"a3",
    "emailAddress":"[email protected]",
}]

Organaziation

[{
    "emailAddress": "[email protected]",
    "name" : "org1"
},
{
    "emailAddress": "[email protected]",,
    "name" : "org1"
},
{
    "emailAddress" : "[email protected]",
    "name" : "org2"
}]

Now I want to fetch all users for organization org1 like below

[{
    "id":1, "name":"a1", "emailAddress":"[email protected]","orgName" : "org1"
},
{
    "id":2, "name":"a2", "emailAddress":"[email protected]","orgName" : "org1"
}]

I have checked debRef and lookup but those are returning in nested

How can I acheive this?

Upvotes: 1

Views: 83

Answers (1)

Ashh
Ashh

Reputation: 46441

You can simply achieve this using $lookup and $project aggregations

If you have mongodb version 3.6 and above

db.users.aggregate([
  { "$lookup": {
    "from": Organisation.collection.name,
    "let": { "emaildid": "$emaildid" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$emaildid", "$$emaildid" ] } } }
    ],
    "as": "organisation"
  }},
  { "$unwind": "$organisation" },
  { "$project": {
    { "id": 1, "name": 1, "emailid": 1, "org": "$organisation.org1" }
  }}
])

If you have mongodb version 3.4 and below

db.users.aggregate([
  { "$lookup": {
    "from": Organisation.collection.name,
    "localField": "emaildid",
    "foreignField": "emaildid",
    "as": "organisation"
  }},
  { "$unwind": "$organisation" },
  { "$project": {
    { "id": 1, "name": 1, "emailid": 1, "org": "$organisation.org1" }
  }}
])

try with $replaceRoot as well

db.Organisation.aggregate([
  { "$match": { "name": "org1" }},
  { "$lookup": {
    "from": Organisation.collection.name,
    "let": { "emailAddress": "$emailAddress", "name": "$name" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$emailAddress", "$$emailAddress" ] } } },
      { "$addFields": { "orgName": "$$name" }}
    ],
    "as": "users"
  }},
  { "$unwind": "$users" },
  { "$replaceRoot": { "newRoot": "$users" } }
])

Upvotes: 2

Related Questions