Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use MongoDBs aggregate `$lookup` as `findOne()`

So as you all know, find() returns an array of results, with findOne() returning just a simply object.

With Angular, this makes a huge difference. Instead of going {{myresult[0].name}}, I can simply just write {{myresult.name}}.

I have found that the $lookup method in the aggregate pipeline returns an array of results instead of just a single object.

For example, I have two colletions:

users collection:

[{
  "firstName": "John",
  "lastName": "Smith",
  "country": 123
}, {
  "firstName": "Luke",
  "lastName": "Jones",
  "country": 321
}]

countries collection:

[{
  "name": "Australia",
  "code": "AU",
  "_id": 123
}, {
  "name": "New Zealand",
  "code": "NZ",
  "_id": 321
}]

My aggregate $lookup:

db.users.aggregate([{
  $project: {
    "fullName": {
      $concat: ["$firstName", " ", "$lastName"]
    },
    "country": "$country"
  }
}, {
  $lookup: {
    from: "countries",
    localField: "country",
    foreignField: "_id",
    as: "country"
  }
}])

The results from the query:

[{
  "fullName": "John Smith",
  "country": [{
    "name": "Australia",
    "code": "AU",
    "_id": 123
  }]
}, {
 "fullName": "Luke Jones",
 "country": [{
   "name": "New Zealand",
   "code": "NZ",
   "_id": 321
 }]
}]

As you can see by the above results, each country is an array instead of a single object like "country": {....}.

How can I have my $lookup return a single object instead of an array since it will only ever match a single document?

like image 719
Fizzix Avatar asked Jun 08 '16 01:06

Fizzix


People also ask

How do you populate with aggregate?

With the latest version of mongoose (mongoose >= 3.6), you can but it requires a second query, and using populate differently. After your aggregation, do this: Patients. populate(result, {path: "patient"}, callback);

How does MongoDB lookup work?

The MongoDB Lookup operator, by definition, “Performs a left outer join to an unshared collection in the same database to filter in documents from the “joined” collection for processing.” Simply put, using the MongoDB Lookup operator makes it possible to merge data from the document you are running a query on and the ...

How do I join two collections in MongoDB?

For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents. For example, if a user requires all grades from all students, then the below query can be written: Students.

Does lookup use index MongoDB?

Starting in MongoDB 5.0, the $eq , $lt , $lte , $gt , and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage. Limitations: Multikey indexes are not used.


4 Answers

You're almost there, you need to add another $project stage to your pipeline and use the $arrayElemAt to return the single element in the array.

db.users.aggregate(
    [
        {   "$project": {     
            "fullName": {       
                "$concat": [ "$firstName", " ", "$lastName"]     
            },
            "country": "$country"   
        }}, 
        { "$lookup": {     
                "from": "countries",     
                "localField": "country",     
                "foreignField": "_id",     
                "as": "countryInfo"   
        }}, 
        { "$project": { 
            "fullName": 1, 
            "country": 1, 
            "countryInfo": { "$arrayElemAt": [ "$countryInfo", 0 ] } 
        }} 
    ]
)
like image 83
styvane Avatar answered Oct 25 '22 18:10

styvane


You can also use "preserveNullAndEmptyArrays"

Like so:

 db.users.aggregate(
        [
            {   "$project": {     
                "fullName": {       
                    "$concat": [ "$firstName", " ", "$lastName"]     
                },
                "country": "$country"   
            }}, 
            { "$lookup": {     
                    "from": "countries",     
                    "localField": "country",     
                    "foreignField": "_id",     
                    "as": "countryInfo"   
            }}, 
            {"$unwind": {
                    "path": "$countryInfo",
                    "preserveNullAndEmptyArrays": true
                }
            },
        ]
    )
like image 41
Ros Avatar answered Oct 25 '22 18:10

Ros


db.users.aggregate([
    {
        $lookup: {
            from: 'countries',
            localField: 'country',
            foreignField: '_id',
            as: 'country'
        }
    },
    {
        $unwind: '$country'
    }
]).pretty()

You can use this mongo query for getting the country object

like image 37
Prakash s Avatar answered Oct 25 '22 19:10

Prakash s


When you don't want to repeat all fields in project, just overwrite the field in question with $addFields:

db.users.aggregate([
    {   "$project": {     
        "fullName": {       
            "$concat": [ "$firstName", " ", "$lastName"]     
        },
        "country": "$country"   
    }}, 
    { "$lookup": {     
        "from": "countries",     
        "localField": "country",     
        "foreignField": "_id",     
        "as": "countryInfo"   
    }},
    { "$addFields": {
        "countryInfo": {
            "$arrayElemAt": [ "$countryInfo", 0 ]
        }
    }}
])
like image 32
and.h Avatar answered Oct 25 '22 18:10

and.h