CODEWITHSUNDEEP
mongodbrestmongodb-query
Ganesh Shenoy
Ganesh Shenoy

Reputation: 629

Mongodb combining multiple queries

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.

Upvotes: 0

Views: 2041

Answers (1)

Gibbs
Gibbs

Reputation: 22956

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.

Upvotes: 1

Related Questions