mongo join between two collections

Question

I have two mongo collections:

defn

"_id" : ObjectId("8570bebcb7db3"), 
"fields" : [    {      
"control" : {   "appearance" : "field-list" },  
"children" : [ {       "bind" : {      "required" : "yes" },   ...

data

"_id" : ObjectId("1570bf18a7db"),
"defn" : ObjectId("8570bebcb7db3"),
"data" : {
        "country" : "",
        "age" : 1,
        "age_unit" : "years", 
},
"label" : "type"

that are joined where defn._id =data.defn

How can I write a query to return data.label for defn._id? The query would be of the form db.defn.find({ data.label where defn._id= "X"})

Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — Philipp, Aug 03 '16 at 19:00

score 3 · Answer 1 · answered Aug 04 '16 at 05:10

If you're using MongoDB 3.2, the $lookup stage can perform the equivalent of a left outer join.

The documentation provides an example of using this operation here.

Example using your data:

db.defn.insert({
  "_id" : "123456", 
  "some_text" : "main document"
})

db.data.insert( { 
  "defn" : "123456",
  "label" : "data we want to access"
})

db.defn.aggregate( [ 
  { "$lookup" : {
     "from" : "data",
     "localField" : "_id", 
     "foreignField" : "defn",
     "as" : "defns"
    }
  } 
])

// Results:
// { 
//   "_id" : "123456", 
//   "some_text" : "main document", 
//   "defns" : [ { 
//      "_id" : ObjectId("57a2cbbbeb99ff285a1f0893"), 
//      "defn" : "123456", 
//      "label" : "data we want to access" 
//    } ] 
// }

score 0 · Answer 2 · answered Feb 24 '18 at 01:25

You might want to try this module @coolgk/mongo, it allows you to join and filter on multiple collections.

Examples

SQL to Mongo Join

Left Join

SELECT * FROM a LEFT JOIN b ON a.b_id = b.id

becomes

model.find({}, {
    join: [ { on: 'b_id' } ]
})

Result:

[{
    _id: '5a8bde4ae2ead929f89f3c42',
    a_name: 'aname1',
    b_id: {
        _id: '5a8bde4ae2ead929f89f3c41',
        b_name: 'bname1'
    }
}, { ... }, ... ]

Inner Join with Constraints

SELECT * FROM a, b WHERE a.b_id = b.id AND b.b_name = 'bname1'

becomes

model.find({}, {
    join: [ { on: 'b_id', filters: { b_name: 'bname1' } } ]
})

Result:

[{
    _id: '5a8bdfb05d44ea2a08fa8a4c',
    a_name: 'aname2',
    b_id: {
        _id: '5a8bdfb05d44ea2a08fa8a4b',
        b_name: 'bname2'
    }
}]

Inner Join on Mulitple Collections

SELECT * FROM a, b, c WHERE a.b_id = b.id AND b.c_id = c.id AND c.c_name = 'cname3'

modela.find({}, {
    join: [{
        on: 'b_id',
        join: [{
            on: 'c_id',
            filters: { c_name: 'cname3' }
        }]
    }]
})

Result:

[{
    _id: '5a8bdfc1b07af22a12cb1f0b',
    a_name: 'aname3',
    b_id: {
        _id: '5a8bdfc1b07af22a12cb1f0a',
        b_name: 'bname3',
        c_id: {
            _id: '5a8bdfc1b07af22a12cb1f09',
            c_name: 'cname3'
        }
    }
}]

mongo join between two collections

2 Answers2

Examples

SQL to Mongo Join