How to query two collections with related data?

Question

I have 2 collections, collection A has some documents like {'id':1,'field':'name'},{'id':1,'field':'age'}，and collection B has some documents like {'_id':1,'name':'alice','age':18,'phone':123},{'_id':2,'name':'bob','age':30,'phone':321}

and I want to find all the document whose '_id' is in collectionA, and just project the corresponding field.

for example:

collection A

{'id':1,'field':'name'},
{'id':1,'field':'age'}

collection B

{'_id':1,'name':'alice','age':18,'phone':123},
{'_id':2,'name':'bob','age':30,'phone':321}

the result is:

{'name':'alice','age':18},

I don't know if there is an easy way to do that？

You can try using a single aggregation query with a [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) stage. Here is one similar post: [How to join multiple collections with $lookup in mongodb](https://stackoverflow.com/questions/35813854/how-to-join-multiple-collections-with-lookup-in-mongodb). — prasad_, Mar 25 '21 at 10:45
@zxCoder Sorry the output was wrong in the first attempt. Check the updated query with output, also what is your MongoDB version? — Dheemanth Bhat, Mar 25 '21 at 22:18

score 0 · Accepted Answer · answered Mar 25 '21 at 10:52

You can use $lookup to join two collection

db.col1.aggregate([
  {
    $match: {
      id: 1
    }
  },
  {
    "$lookup": {
      "from": "col2",
      "localField": "id",
      "foreignField": "_id",
      "as": "listNames"
    }
  },
  {
    $project: {
      listNames: {
        $first: "$listNames"
      }
    }
  },
  {
    $project: {
      _id: 0,
      name: "$listNames.name",
      age: "$listNames.age"
    }
  }
])

Mongo Playground: https://mongoplayground.net/p/E-0WvK_SUS_

Dheemanth Bhat · Answer 2 · 2021-03-25T22:29:15.617

So the idea is:

Convert the documents in to key, value pair for both the collections using $objectToArray.
Then perform a join operation based on key k and (id <-> _id) using $lookup.
Replace the result as root element using $replaceRoot.
Convert array to object using $arrayToObject and again $replaceRoot.

Query:

db.colB.aggregate([
    {
        $project: {
            temp: { $objectToArray: "$$ROOT" }
        }
    },
    {
        $lookup: {
            from: "colA",
            let: { temp: "$temp", colB_id: "$_id" },
            pipeline: [
                {
                    $addFields: {
                        temp: { k: "$field", v: "$id" }
                    }
                },
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $in: ["$temp.k", "$$temp.k"] },
                                { $eq: ["$temp.v", "$$colB_id"] }
                            ]
                        }
                    }
                },
                {
                    $replaceRoot: {
                        newRoot: {
                            $first: {
                                $filter: {
                                    input: "$$temp",
                                    as: "item",
                                    cond: { $eq: ["$field", "$$item.k"] }
                                }
                            }
                        }
                    }
                }
            ],
            as: "array"
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayToObject: "$array" }
        }
    }
]);

Output:

{
    "name" : "alice",
    "age" : 18
}

How to query two collections with related data?

2 Answers2