Mongodb query multiple collection

Question

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?

using mongoose or mongodb driver ?

Kaushik Makwana
– Kaushik Makwana

2018-07-06 08:53:03 +00:00
Commented Jul 6, 2018 at 8:53 — Kaushik Makwana
– Kaushik Makwana, Commented Jul 6, 2018 at 8:53

Ashh · Accepted Answer · 2018-07-06 12:16:56Z

2

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

edited Jul 6, 2018 at 12:16

answered Jul 6, 2018 at 7:02

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ashh Over a year ago

You need to aggregate with users collection and $lookup with organisation collection... See the updated answer... and make sure you put correct collection name inside $lookup i.e. in Organisation.collection.name

Md. Parvez Alam Over a year ago

I manage to get data using ----- db.userorganizations.aggregate([ { $match: { "organization" : "test", }}, {$lookup: { from: "users", localField: "emailAddress", foreignField: "emailAddress", as: "userdetails"} }

Ashh Over a year ago

Check the updated answer and please delete all the comments

Md. Parvez Alam Over a year ago

unfortunately its not working, Could you please help me how can I get required fields from both collection in this query ----- b.userorganizations.aggregate([ { $match: { "organization" : "test", }}, {$lookup: { from: "users", localField: "emailAddress", foreignField: "emailAddress", as: "userdetails"} }---

Ashh Over a year ago

Sorry not possible because I have put them into my collections and checked and get same output as you posted in your question... Please post the query in your question which you have tried

|

Collectives™ on Stack Overflow

Mongodb query multiple collection

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related