CODEWITHSUNDEEP
mongodblookup
user6401131
user6401131

Reputation:

Mongodb $lookup joins all collection instead of matching object

so I am trying to do a $lookup with Mongodb but I have a strange output. I have two collections, "sites" and "consumptions".

sites :

{
    "_id" : ObjectId("5b26db6e7f59e825909da106"),
    "siteId" : 49,
    "industry" : "Commercial Property",
    "sub_industry" : "Shopping Center/Shopping Mall",
    "square_feet" : 497092,
    "latitude" : 41.2161756,
    "longitude" : -78.14809154,
    "timezone" : "America/New_York",
    "timezone_offset" : "-04:00",
    "__v" : 0
}

consumptions :

{
    "_id" : ObjectId("5b26db907f59e825909f3d2a"),
    "timestamp" : 1325382000,
    "dttm_utc" : ISODate("2012-01-01T00:40:00Z"),
    "value" : 2.8956,
    "estimated" : 0,
    "anomaly" : "",
    "site" : [
            {
               "_id" : ObjectId("5b26db727f59e825909da16a")
            }
    ],
    "__v" : 0
}

This is the $lookup I am trying to do :

db.consumptions.aggregate([
  {
   $lookup:
     {
       from: "sites",
       localField: "site.id",
       foreignField: "id",
       as: "site"
     }
  }
])

The expected output would be to have the detail of the site in each consumption :

{
    "_id" : ObjectId("5b26db907f59e825909f3d2a"),
    "timestamp" : 1325382000,
    "dttm_utc" : ISODate("2012-01-01T00:40:00Z"),
    "value" : 2.8956,
    "estimated" : 0,
    "anomaly" : "",
    "site" : [
        {
            "_id" : ObjectId("5b26db6e7f59e825909da106"),
            "siteId" : 49,
            "industry" : "Commercial Property",
            "sub_industry" : "Shopping Center/Shopping Mall",
            "square_feet" : 497092,
            "latitude" : 41.2161756,
            "longitude" : -78.14809154,
            "timezone" : "America/New_York",
            "timezone_offset" : "-04:00",
            "__v" : 0
        }
    ],
    "__v" : 0
}

This is the output I am getting with the $lookup :

{
    "_id" : ObjectId("5b26db907f59e825909f3d2a"),
    "timestamp" : 1325382000,
    "dttm_utc" : ISODate("2012-01-01T00:40:00Z"),
    "value" : 2.8956,
    "estimated" : 0,
    "anomaly" : "",
    "site" : [
            {
                    "_id" : ObjectId("5b26db6e7f59e825909da0f3"),
                    "siteId" : 6,
                    "industry" : "Commercial Property",
                    "sub_industry" : "Shopping Center/Shopping Mall",
                    "square_feet" : 161532,
                    "latitude" : 34.78300117,
                    "longitude" : -106.8952497,
                    "timezone" : "America/Denver",
                    "timezone_offset" : "-06:00",
                    "__v" : 0
            },
            {
                    "_id" : ObjectId("5b26db6e7f59e825909da0f4"),
                    "siteId" : 8,
                    "industry" : "Commercial Property",
                    "sub_industry" : "Shopping Center/Shopping Mall",
                    "square_feet" : 823966,
                    "latitude" : 40.32024733,
                    "longitude" : -76.40494239,
                    "timezone" : "America/New_York",
                    "timezone_offset" : "-04:00",
                    "__v" : 0
            }, ... (all the sites details are listed)
      ],
      "__v" : 0
}

Thank you in advance for your help !

Upvotes: 1

Views: 57

Answers (2)

Ashh
Ashh

Reputation: 46451

You need to first $unwind the site array to match site._id to the foreign field _id and then $group to rolling back into the arrays again.

db.collection.aggregate([
  { "$unwind": "$site" },
  { "$lookup": {
    "from": Site.collection.name,
    "localField": "site._id",
    "foreignField": "_id",
    "as": "site"
  }},
  { "$unwind": "$site" },
  { "$group": {
    "_id": "$_id",
    "value": { "$first": "$value" },
    "estimated": { "$first": "$estimated" },
    "anomaly": { "$first": "$anomaly" },
    "timestamp": { "$first": "$timestamp" },
    "dttm_utc": { "$first": "$dttm_utc" },
    "site": { "$push": "$site" }
  }}
])

And if you have mongodb 3.6 then you can try this

db.collection.aggregate([
  { "$unwind": "$site" },
  { "$lookup": {
    "from": Site.collection.name,
    "let": { "siteId": "$site._id" },
    "pipeline": [
       { "$match": { "$expr": { "$eq": [ "$_id", "$$siteId" ] } } }
     ],
     "as": "site"
  }},
  { "$unwind": "$site" },
  { "$group": {
    "_id": "$_id",
    "value": { "$first": "$value" },
    "estimated": { "$first": "$estimated" },
    "anomaly": { "$first": "$anomaly" },
    "timestamp": { "$first": "$timestamp" },
    "dttm_utc": { "$first": "$dttm_utc" },
    "site": { "$push": "$site" }
  }}
])

Make sure you should put Site.collection.name correctly

Upvotes: 1

Sav74
Sav74

Reputation: 88

I think that The $lookup doesn't work directly with an array.

try using $unwind first.

Upvotes: 0

Related Questions