Lookup on multiple collections using the same field in the target collection

Working with Data
1

I have 3 collections:

  • jobs
  • resources1
  • resources2

The jobs collections contains the field resourceid which is a bson id that will point to a document in resources1 or resources2.

I want to write a aggregation framework query that will allow me to query these collections all together while leveraging all available indexes. I have tried adding multiple lookups here:

db.jobs.aggregate([
    {
        "$lookup": {
            "from": "resources1",
            "localField": "resourceid",
            "foreignField": "_id",
            "as": "r1"
        }
    },
    {
        "$unwind": "$r1"
    },
    {
        "$lookup": {
            "from": "resources2",
            "localField": "resourceid",
            "foreignField": "_id",
            "as": "r2"
        }
    },
    {
        "$unwind": "$r2"
    }
])

However, when I do this no results are returned. I see that the $facet stage is a suggested approach, but when I do this no indexes are used so that query takes too long. Here is that query:

db.jobs.aggregate([
    {
        "$facet": {
            "r1": [
                {
                    "$lookup": {
                        "from": "resources1",
                        "localField": "resourceid",
                        "foreignField": "_id",
                        "as": "resource"
                    }
                },
                {
                    "$unwind": "$resource"
                }
            ],
            "r2": [
                {
                    "$lookup": {
                        "from": "resources2",
                        "localField": "resourceid",
                        "foreignField": "_id",
                        "as": "resource"
                    }
                },
                {
                    "$unwind": "$resource"
                }
            ]
        }
    },
    {
        "$project": {
            "jobs": {
                "$concatArrays": [
                    "$r1",
                    "$r2"
                ]
            }
        }
    },
    {
        "$unwind": "$jobs"
    },
    {
        "$replaceRoot": {
            "newRoot": "$jobs"
        }
    }
])
2

Hi @Kyle_Barnes1, without knowing your document sizes and structures I’d try using a unionWith stage, perhaps something like:

db.resources1.aggregate([
  {
    $lookup: {
      from: "jobs",
      localField: "resourceId",
      foreignField: "resourceId",
      as: "jobs",
    },
  },
  {
    $unionWith: {
      coll: "resources2",
      "pipeline": [{
        $lookup: {
          from: "jobs",
          localField: "resourceId",
          foreignField: "resourceId",
          as: "jobs",
        }
      }]
    },
  },
])

So you can either union the two lookups, or union the two resources table then lookup, depending on your structures etc. Unions have duplicates so if that’s an issue, run a grouping stage to remove those as required.

Hope that helps

Craig.

1 Like