0

I'm trying to use $lookup for aggregation. My "quotes" has documents for each quote as below

{
    "_id" : "11223344",
    "_etag" : ObjectId("58b7fe8c3df03c1594ab0c45"),
    "vehicles" : [ 
        "1122334455", 
        "1122334456"
    ]
}

now the vehicles is another collection as below

{
    "_id" : "1122334455",
    "referenceNumber" : "11223344",
    "sequenceNumber" : 15,
    "status" : {
        "statusCode" : "consectetur amet fugiat id",
        "statusDesc" : "exercitation"
    },
    "trackerInstalled" : true,
    "vehicleDetails" : {
        "year" : "2012",
        "make" : "Toyota",
        "model" : "Corrola"
    },
    "riskPremium" : "99.00",
    "_etag" : ObjectId("58b91f6d1cdd923b3ce7f091")
}

{
    "_id" : "1122334456",
    "referenceNumber" : "11223344",
    "sequenceNumber" : 16,
    "status" : {
        "statusCode" : "consectetur amet fugiat id",
        "statusDesc" : "exercitation"
    },
    "trackerInstalled" : true,
    "vehicleDetails" : {
        "year" : "2012",
        "make" : "BMW",
        "model" : "320d"
    },
    "riskPremium" : "199.00"
}

what i want is that when a user query as GET /quotes/1122334455, i should send him one quote documents with all the vehicles joined from vehicles collection as below,

{
    "_id" : "11223344",
    "_etag" : ObjectId("58b7fe8c3df03c1594ab0c45"),
    "vehicles" : "1122334455",
    "vehicle_doc" : [ 
        {
            "_id" : "1122334455",
            "referenceNumber" : "11223344",
            "sequenceNumber" : 15,
            "status" : {
                "statusCode" : "consectetur amet fugiat id",
                "statusDesc" : "exercitation"
            },
            "trackerInstalled" : true,
            "vehicleDetails" : {
                "year" : "2012",
                "make" : "Toyota",
                "model" : "Corrola"
            },
            "riskPremium" : "99.00",
            "_etag" : ObjectId("58b91f6d1cdd923b3ce7f091")
        },
{
            "_id" : "1122334456",
            "referenceNumber" : "11223344",
            "sequenceNumber" : 16,
            "status" : {
                "statusCode" : "consectetur amet fugiat id",
                "statusDesc" : "exercitation"
            },
            "trackerInstalled" : true,
            "vehicleDetails" : {
                "year" : "2012",
                "make" : "BMW",
                "model" : "320d"
            },
            "riskPremium" : "199.00"
        }
    ]
}

I used below aggregation

db.quotes.aggregate([    
    { $unwind: "$vehicles" },        
    { $lookup: {
        from: "vehicles",
        localField: "vehicles",
        foreignField: "_id",
        as: "vehicle_doc"
    } }
])

but it is giving me two documents for same quote and each document has one vehicle included. how can i merge both the documents to only have the array of vehicle in one quote.

I checked the referred question and the difference with my issue is that i need to retain rest of the document information from left collection, the idea in the solution assumes that there is no other information to project other than _id but i need to pass back rest of the quote information also.

Genx
  • 1
  • 1
  • hi, @chridam i actually want to retain rest of the fields from left collection, while the group stage in the answer only gives me the _id of the left collection. how can i keep rest of the information and also merge the documents together into array? – Genx Mar 20 '17 at 09:58
  • You'd need to run `db.quotes.aggregate([ /* Unwind the source */ { "$unwind": "$vehicles" }, /* Do the lookup matching */ { "$lookup": { "from": "vehicles", "localField": "vehicles", "foreignField": "_id", "as": "vehicle_doc" } }, /* Unwind the result arrays */ { "$unwind": "$vehicle_doc" }, /* Group back to arrays */ { "$group": { "_id": "$_id", "vehicle_doc": { "$push": "$vehicle_doc" }, "vehicles": { "$push": "$vehicles" }, "_etag": { "$first": "$_etag" } } } ])` – chridam Mar 20 '17 at 10:15
  • Lets say i have 20-30 fields in the left document as simple fields and i want to have them in my final output, do i have to write each and every field? or could i just somehow get them all anyway? – Genx Mar 20 '17 at 10:30
  • Unfortunately you'd have to write each and every one as there's no mechanism (yet) for the `$group` pipeline to return each field with just a single operator. – chridam Mar 20 '17 at 10:44

0 Answers0