How to combine a collection with the object of a different collection without relation in MongoDB?

user_8445310 0
Viewed 68

I have 2 collections;

//db.cities.find({})

/* 1 */
{
    "_id" : ObjectId("/*...*/"),
    "Name" : "Istanbul"
}

/* 2 */
{
    "_id" : ObjectId("/*...*/"),
    "Name" : "Ankara"
}

/* 3 */
{
    "_id" : ObjectId("/*...*/"),
    "Name" : "Izmir"
}

/* 4 */
{
    "_id" : ObjectId("/*...*/"),
    "Name" : "Eskisehir"
}

//db.dates.find({})

/* 1 */
{
    "_id" : "Turkey",
    "dateTime": "2021-09-03 10:25"
}

/* 2 */
{
    "_id" : "England",
    "dateTime": "2021-09-03 08:25"
}

I want to combine two collections but not like inner join.

Here is the result I want to see;

//the output that I need

{
    "_id" : ObjectId("/*...*/"),
    "Cities": [
        {
            "_id" : ObjectId("/*...*/"),
            "Name" : "Istanbul"
        },
        {
            "_id" : ObjectId("/*...*/"),
            "Name" : "Ankara"
        },
        {
            "_id" : ObjectId("/*...*/"),
            "Name" : "Izmir"
        },
        {
            "_id" : ObjectId("/*...*/"),
            "Name" : "Eskisehir"
        }
    ],
    "DateTime" : "2021-09-03 10:25"
}

As a result, I don't want to see the DateTime in all cities elements. How can I get this result in MongoDB v3.2?

Note 1: The cities collection doesn't have the Country field. I can write Turkey as hardcoded in the query.

Note 2: I prefer aggregate instead of map-reduce if is it possible.

aggregation-framework mongodb-query mongodb
1 Answers

Since 3.6, MongoDB allows execute Subqueries

Try this one:

db.dates.aggregate([
  {
    "$lookup": {
      "from": "cities",
      "pipeline": [],
      "as": "Cities"
    }
  },
  {
    $match: {
      _id: "Turkey"
    }
  },
  {
    "$addFields": {
      _id: "$$REMOVE",
      "Country": "Turkey"
    }
  }
])

MongoPlayground

user_37104900
Related