1

I have three collections: parent, child1, child2.

parent collection:

[{_id:1}, {_id:2}, {_id:3}]

child1 collection:

[{_id:1, child1:'a', parent:1}, {_id:2, child1:'b', parent:1}, {_id:3, child1:'c', parent:2}]

child2 collection:

[{_id:1, child1:'d', parent:2}, {_id:2, child1:'e', parent:3}]

collections child1 and child2 referenced to parent collection.

now, I want a query in mongoose to get a result like this:

[
 {
  _id:1,
  child1:[{_id:1, child1:'a'}, {_id:2, child1:'b'}],
  child2:[],
 },
 {
  _id:2,
  child1:[{_id:2, child1:'c'}],
  child2:[{_id:1, child1:'d'}],
 },
 {
  _id:3,
  child1:[],
  child2:[{_id:2, child1:'e'}],
 },
]

my try in Aggregate is work correctly:

db.parent.aggregate([
  {
    "$lookup": {
      "from": "child1",
      "localField": "_id",
      "foreignField": "parent",
      "as": "child1"
    }
  },
  {
    "$lookup": {
      "from": "child2",
      "localField": "_id",
      "foreignField": "parent",
      "as": "child2"
    }
  }
])

Mongo Playground

This works when the collections are in the same database but my collections in the separate database.

e.g: parent collection in parent database and child1 and child2 collections in child database.

Do you have any idea for use aggregation across multiple databases or how can I use populate to solve this problem?

J.F.
  • 13,927
  • 9
  • 27
  • 65
mehdi parastar
  • 737
  • 4
  • 13
  • 29
  • 1
    what have you tried so far? try aggregation with [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) you can use multiple lookups in single query. – turivishal Jan 06 '21 at 07:34
  • my collections in seperate databeses e.g: parent collection in one database and child1 and child2 coll in another database.only I can use poplulate – mehdi parastar Jan 06 '21 at 07:42
  • Please update this details in your question as well not in comment, i don't think it is possible in single query look at this open [Jira ticket-34935](https://jira.mongodb.org/browse/SERVER-34935) and similar [question1](https://stackoverflow.com/questions/39222798/is-it-possible-to-do-a-lookup-aggregation-between-two-databases-in-mongodb), [question2](https://stackoverflow.com/questions/13278814/mongodb-cross-database-query) – turivishal Jan 06 '21 at 08:02

1 Answers1

0

As mongoDB docs says about $lookup:

Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing.

So no, it's not possible do it accross DBs.

But, as an addition, using Mongo exists db.getSiblingDB() option.

Docs here where says:

Used to return another database without modifying the db variable in the shell environment.

So, this is not a $lookup but is an approach to have another Data Base data.

J.F.
  • 13,927
  • 9
  • 27
  • 65