Fetch data from 2 collections in mongodb in single query

Question

I wanted to fetch data from 2 independent collections and sort the results based on date through a single query. Is that even possible in mongodb? I have collections:

OrderType1

{
    "id": "1",
    "name": "Hello1",
    "date": "2016-09-23T15:07:38.000Z"
},
{
    "id": "2",
    "name": "Hello1",
    "date": "2015-09-23T15:07:38.000Z"
}

OrderType2

    {
        "id": "3",
        "name": "Hello3",
        "date": "2012-09-23T15:07:38.000Z"
    },
    {
        "id": "4",
        "name": "Hello4",
        "date": "2018-09-23T15:07:38.000Z"
    }

Expected Result

[
    {
        "id": "3",
        "name": "Hello3",
        "date": "2012-09-23T15:07:38.000Z"
    },
    {
        "id": "2",
        "name": "Hello1",
        "date": "2015-09-23T15:07:38.000Z"
    },
    {
        "id": "1",
        "name": "Hello1",
        "date": "2016-09-23T15:07:38.000Z"
    },

    {
        "id": "4",
        "name": "Hello4",
        "date": "2018-09-23T15:07:38.000Z"
    }
]

Now, I want to fetch both types of orders in a single query sorted by date.

It should be an array of combined data from both collections sorted by date. I have edited the question and added expected response/ I can do this in using 2 queries, get the data and sort it. But I am not sure if if can be done in 1 query. — helloworld
– helloworld, Commented Oct 9, 2018 at 15:45

Ashh · Accepted Answer · 2018-10-09 16:25:44Z

1

You can try below aggregation with mongodb 3.6 and above but I think you should use two queries because for the large data set $lookup pipeline will breach BSON limit of 16mb. But also It depends upon your $match condition or $limit. If they are applied to the $lookup pipeline then your aggregation would work perfectly.

db.OrderType1.aggregate([
  { "$limit": 1 },
  { "$facet": {
    "collection1": [
      { "$limit": 1 },
      { "$lookup": {
        "from": "OrderType1",
        "pipeline": [{ "$match": { } }],
        "as": "collection1"
      }}
    ],
    "collection2": [
      { "$limit": 1 },
      { "$lookup": {
        "from": "OrderType2",
        "pipeline": [{ "$match": { } }],
        "as": "collection2"
      }}
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": [
        { "$arrayElemAt": ["$collection1.collection1", 0] },
        { "$arrayElemAt": ["$collection2.collection2", 0] },
      ]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" } }
])

edited Oct 9, 2018 at 16:25

answered Oct 9, 2018 at 15:50

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

helloworld Over a year ago

Thanks. For moderate sized collections, which approach would be more optimal in terms of query time, this single query or 2 queries and then sorting?

Ashh Over a year ago

I think you should use two queries because for the large data set $lookup will breach BSON limit of 16mb. But also It depends upon your $match condition or $limit.

Collectives™ on Stack Overflow

Fetch data from 2 collections in mongodb in single query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related