0

I have a mongo collection with a composite Id containing docs that looks something like this:

{
    "_id" : {
        "Id1" : NumberLong(123),
        "Id2" : NumberInt(45),
        "Guid1" : "Guid1"
        "Guid2" : "Guid2"
    },
    "Field1" : NumberInt(1),
    "Field2" : 0.0,
    "Field3" : "String value",
}

I want to select multiple documents (thousands) from this collection by providing a list of only _Id.Id1 and _Id.Id2 pairs. I'm not sure how to do this in MongoDB or in the .Net driver, let alone if its even possible.

If my question isn't clear, I'll pseudo it as best I can:

class QueryCriteria
{
   Int64 Id1,
   Int Id2,
}

Public GetList(List<QueryCriteria> criteria)
{
   List<ResultModel> results = _collection.Find<ResultModel>(
      (x, criteria) => 
          x._id.Id1 == criteria.Id1 && 
          x._id.Id2 == criteria.Id2 )
}

SQL Pseudo (If SQL also had the ability to have a column be an entire object)

SELECT      Table1.*

FROM        Table1      T1

JOIN        Table2      T2
ON          T1._id.Id1 = T2.Id1
AND         T1._id.Id2 = T2.Id2

Some context: Selecting all documents from mongo and filtering in memory using linq is taking to long, I'm chasing performance here by trying to hive off some of the processing to Mongo.

Swifty
  • 1,422
  • 2
  • 18
  • 38
  • In mongo, you can write a query that looks like `{"_id.Id1": 100, "_id.Id2": 42}` to select those documents using dots to separate the fields. Performance here will depend on whether the collection has indexes on those fields. – klhr Jun 17 '19 at 18:29
  • If you only have the lists, you can use $in to provide two different lists `{"a.b": {$in: [1, 2, 3], "a.c": {$in: [4, 5, 6] }`, or if you know which pairs match up, you can use $or `{$or: [{a.b: 1, a.c: 4}, {a.b: 2, a.c: 5}] }` – klhr Jun 17 '19 at 19:01
  • I'm not quite sure I understand what you mean in your second comment. I have another collection containing Id1 and Id2 pairs. So I'm selecting from that collection, then with that result I want to select the corresponding docs from the second collection using the Id pairs I got from the first query. – Swifty Jun 17 '19 at 19:23
  • 1
    @willis I added some SQL pseudo to give a better idea of what I'm trying to achieve – Swifty Jun 17 '19 at 19:28
  • OHHHH, sorry -- I completely misunderstood your question. You'll want to use the $lookup operator in the aggregation pipeline. https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb has a good answer for a similar problem, and https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/ has some good examples of aggregation equivalents of mongo queries – klhr Jun 17 '19 at 22:00
  • https://stackoverflow.com/questions/37086387/multiple-join-conditions-using-the-lookup-operator has a good example of how to join on 2 different fields, but you could also just update the collection so that it has a 3rd field of id1 + id2 – klhr Jun 17 '19 at 22:01

0 Answers0