Elastic search query for SQL query

Question

I have SQL table XYZ with below data:

P------     S-----     R--  
SAMSUNG     GALAXY     1
SAMSUNG     GALAXY     2
SAMSUNG     GALAXY     10
SAMSUNG     GALAXY     9
APPLE       IOS        12
APPLE       IOS        9

I have below SQL query:

select * from XYZ where P='SAMSUNG' AND S='GALAXY' AND R IN (1,2)

It returns all the records that match column P with value "SAMSUNG" AND column S with "GALAXY" AND column R with values 1 or 2.

OUTPUT:

SAMSUNG     GALAXY     1
SAMSUNG     GALAXY     2

Now I am trying to prepare an Elastic Search query for the above behavior but the result is not as expected:

{'query': 
       {'bool': 
              {'must': [{'match': {'P' : 'SAMSUNG'}}],
               'must': [{'match': {'S' : 'GALAXY'}}],
               'must': [{'match': {'R' : '1'}}, {'match': {'R' : '2'}}]
               }
        }
}

The above query returns empty output. But If I specify only 'P' it works:

{'query': 
       {'bool': 
              {'must': [{'match': {'P' : 'APPLE'}}]}
        }
}

This returns below output

APPLE       IOS        12
APPLE       IOS        9

Not sure what is wrong with my first query. Could any one help how to use "must", "should" with elastic search queries?

Answer 1

You need to use a combination of bool/must queries, to achieve your result

must is equivalent to the logical AND operator and you can use terms query that will act as IN. You need to wrap both the match query and terms query inside the must clause, as in your SQL query all the conditions are combined using AND operator.

---

Adding a working example with index data, search query, and search result

Index Data:

{
  "P": "SAMSUNG",
  "S": "GALAXY",
  "R": 1
}
{
  "P": "SAMSUNG",
  "S": "GALAXY",
  "R": 10
}
{
  "P": "SAMSUNG",
  "S": "GALAXY",
  "R": 2
}
{
  "P": "SAMSUNG",
  "S": "GALAXY",
  "R": 9
}
{
  "P": "APPLE",
  "S": "IOS",
  "R": 12
}
{
  "P": "APPLE",
  "S": "IOS",
  "R": 9
}

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "P": "SAMSUNG"
          }
        },
        {
          "match": {
            "S": "GALAXY"
          }
        },
        {
          "terms": {
            "R": [
              1,
              2
            ]
          }
        }
      ]
    }
  }
}

Search Result:

 "hits": [
      {
        "_index": "67024741",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.8836654,
        "_source": {
          "P": "SAMSUNG",
          "S": "GALAXY",
          "R": 1
        }
      },
      {
        "_index": "67024741",
        "_type": "_doc",
        "_id": "3",
        "_score": 1.8836654,
        "_source": {
          "P": "SAMSUNG",
          "S": "GALAXY",
          "R": 2
        }
      }
    ]