MongoDB 3.2 $lookup using multiple localFields referencing multiple foreignFields

[Lee Parayno]

Hello,

I’m testing out MongoDB 3.2.0-rc6 and it’s $lookup functionality in the aggregation framework. All the documentation I’ve seen to this point shows how to lookup from a “right” collection using a single localField, and linking to a single foreign field.  A coworker said he saw it being possible at MongoDB Days : Silicon Valley to use lookup to have multiple localFields referencing multiple foreignFields, but he didn’t remember the syntax on the slide.

I’ve tried several variations to no avail:

Using separate localField and foreignField keys:

db.Person.aggregate([

{ $lookup : {

from: "Person",           

localField: "_id",           

localField:  “businessGroup",

foreignField:  “personId",  

foreignField:  “businessGroupCode",         

as: "person"     

} }

])

This executed, but I believe only uses the last reference localField and foreignField to perform the join.

Embedded Document

db.Person.aggregate([

{ $lookup : {

from: "Person",           

localField: { “_id” : 1,“businessGroup” : 1 },            

foreignField: { “personId” : 1,  “businessGroupCode” : 1 } ,

as: "person"      

} }

])

Array

db.Person.aggregate([

{ $lookup : {

from: "Person",           

localField: [ "_id", "lastAsOf" ], 

foreignField: [ "dsid", "asOf" ],           

as: "person"     

} }

])

// This one gave an error stating “Arguments to $lookup must be strings”

I haven’t found any documentation or Jira entries that detail how this might be possible.  Anybody have an idea about this?

Lee

[Asya Kamsky]

Currently $lookup functionality is limited and there is no way to specify more than a single equality condition in the $lookup itself.   I'm not sure what exactly your coworker may have been referring to.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/F9DF41E5-0D53-4E4C-B509-9FA080C112A0%40gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers

[Lee]

Thanks, I checked all the slides posted on SlideShare from MongoDB Days : Silicon Valley and I found the same to be true.  I did notice that on Eliot Horowitz's Keynote slides, that there was a different syntax, that was intriguing:

db.mdbdays_attendeees.aggregate([

 { $lookup : { 

   "from" : "cloud_users", 

   on: { email : "$email" }, 

   as: "cloud_user_doc" 

   } 

 },

 { $match : { "cloud_user_doc.active" : true } },

 { $unwind : "$cloud_user_doc.groups" },

 { $lookup : { 

   "from" : "cloud_hosts", 

   on: { group_id: "$cloud_user_doc.groups"}, 

   as: "host"

   }

 },

 { $match : { last_ping : {$gte : new ISODate("2015-06-01Z00:00:00") } } },

 { $group : { _id : "$version", count : { $sum : 1}}}

])

This "on : <document>", syntax seems like it would be ideal and also allow for an easy extension to multiple keys, and also match the convention of source key to named value pattern present in the find syntax.

Lee

[Lee]

Oh, and a side note on the "on : <document>" syntax, I could not get it to work on 3.2.0-RC6, after cleaning up some of the syntax errors from the keynote slides.

// Eliot Horowitz's Keynote Example of $lookup

db.mdbdays_attendees.insert({ first : "Eliot", last : "horowitz", email : "redacted", zip : "10024", title : "CTO" })

db.cloud_days.insert({ first : "Eliot", last : "horowitz", email : "redacted",

  groups : [ 12321, 51234 ]})

db.cloud_hosts.insert({ hostname: "foo.mongodb.com", group_id : 51234, last_ping : new ISODate("2015-06-02Z05:42:12"), version : "3.0.3"})

db.mdbdays_attendeees.aggregate([

 { $lookup : { 

   "from" : "cloud_users", 

   on: { email : "$email" }, 

   as: "cloud_user_doc" 

   } 

 },

 { $match : { "cloud_user_doc.active" : true } },

 { $unwind : "$cloud_user_doc.groups" },

 { $lookup : { 

   "from" : "cloud_hosts", 

   on: { group_id: "$cloud_user_doc.groups"}, 

   as: "host"

   }

 },

 { $match : { last_ping : {$gte : new ISODate("2015-06-01Z00:00:00") } } },

 { $group : { _id : "$version", count : { $sum : 1}}}

])

On Thursday, December 3, 2015 at 3:55:37 PM UTC-8, Lee wrote:

[Asya Kamsky]

Lee,

Those slides were prepared for MongoDB World back in early June before the syntax for $lookup was finalized.

The only syntax that 3.2.0 supports inside $lookup is "from" collection, localField, foreignField which will be equal and "as" to indicate field name for the matching documents.

At this point you will then need to $unwind and $match after the $lookup.   The aggregation pipeline already folds $unwind into the $lookup stage, and this ticket is tracking folding the $match fields that apply to foreign collection into the $lookup as well which would improve performance.   It's not the same as allowing multiple field matching on $lookup so I would encourage you to open such a feature request in Jira - I couldn't immediately find it.   

Unfortunately if you want to $match based on another field in the existing document, that can only be done via $project plus $match - at least until one of https://jira.mongodb.org/browse/SERVER-2507, https://jira.mongodb.org/browse/SERVER-2549, https://jira.mongodb.org/browse/SERVER-2702, - though there might be some duplication there.

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/f74ce405-f7c6-4a4a-a9ad-c1682b5e8096%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.