Mongodb combining multiple queries

Question

I have a REST API where I get search params like below.

API query

{
   [
      {
         "state": "abc",
         "country": "IN"
      },
      {
         "state": "def",
         "country": "US"
      }
   ]
}

Mongodb schema

id | description | state | country
1  |  India      | abc   | IN
2  |  USA        | def   | US

I am looking for an optimized query to get the description for each API query row using one mongodb query. I am looking at nested and.

Edit: I am not looking for sorted response.

 {
      $and: { [
          $and: [ 
               {"state": "abc", "country": "IN"},
               {"state": "def", "country": "US"}
          ]
     ] }
}

I would like to know any better approach of querying to improve query performance, a compound index for state and country field? Any pointers are highly appreciated.

Gibbs · Accepted Answer · 2020-08-11 10:53:34Z

2

You can use $and. Syntax should be as below.

{
   "$and": [
     {"$and": {"state": "abc", "country": "IN"}}, 
     { "$and": {"state": "def", "country": "US"}} 
  ] 
}

If you have an index one {state, country} , it will use if you provide them in the order.

Read about index prefixes

If you have done analysis on the frequent requests, you can order the values stored in the index for faster retrieval. Refer. If you have request that falls on country names which falls at the first set, you can store them a to z in the index. Otherwise reverse order.

Even if you only have state index and country index separately, then there are chances that it would do index intersection.in this case, you don't need compound index.

I suggest you to explain() your query to understand along with all of your match conditions, if you have many.

edited Aug 11, 2020 at 10:53

answered Aug 11, 2020 at 10:47

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

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

3 Comments

Ganesh Shenoy Over a year ago

The insert and query to this collection is in order of 100s per second. Since both read and write being there we are also looking at secondary reads. Considering writes being there and indexed needs to be created and ordered, does the approach of indexing help the use case. Thanks. Surely will be looking at the explain plan.

Ganesh Shenoy Over a year ago

One more point, that I am not looking for sorted responses.

Gibbs Over a year ago

OK, there won't be any problem if you have enough cores, shards, servers even with 100s of read per second. I would suggest you to choose individual indices and sure that mongo ll use index intersection.

Collectives™ on Stack Overflow

Mongodb combining multiple queries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related