How to create "in" operator query for multiple fields?

Question

I have a collection

db.list.insert({ first: "apple", second: "banana" });
db.list.insert({ first: "apple", second: "tree" });
db.list.insert({ first: "orange", second: "tree" });
db.list.insert({ first: "medal", second: "sport" });

I want to query for documents based on these two fields and fetch documents only when both fields match.

I have tried the following query

db.list.find(
  $or: [
     { first: 'apple', second: { $in: ['tree', 'banana']}},
     { first: 'orange', second: { $in: ['tree']}}
  ],
).limit(5);

and added indexes for

{first: 1, second: 1}

In the query plan, it generates same number of plans as the number of predicates in the "or" query. There are about 2 millions of documents and if it does not get its 5 document in the first predicate then it fetches all the documents matching all the predicates and the query times out. Is there any other efficient way to do this?

there are other ways too but filters would remains same. Can you tell number of entries in collection and number of predicates in your query results timed out ? — Ramraj Patel, Jun 13 '19 at 13:38
Number of entries are around 2 million and it times out for about 5 predicates in "or" query. — Ajinkya, Jun 13 '19 at 17:00

score 0 · Answer 1 · answered Jun 14 '19 at 08:07

You can use $facet using mongodb aggregation pipeline. This will make sure documents are getting fetched only once. Here is the sample query

    db.list.aggregate([
    {
        $facet: {
            "condition_1": [
                {
                    "$match": { first: 'apple', second: { $in: ['tree', 'banana']}}
                },
                {
                    "limit": 5
                }
            ],
            "condition_2": [
                {
                    "$match": { first: 'orange', second: { $in: ['tree']}}
                },
                {
                    "limit": 5
                }
            ]
        }
    }
], {allowDiskUse: true})

How to create "in" operator query for multiple fields?

1 Answers1