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?

Bhavya · Accepted Answer · 2021-04-09 16:59:18Z

2

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
        }
      }
    ]

edited Apr 9, 2021 at 16:59

answered Apr 9, 2021 at 16:54

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

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

6 Comments

PJ47 Over a year ago

Great. This worked. Thank You Very Much. If possible could you please point some URL which explains must/should/filter of elastic search queries. I tried some on google search but couldn't get much success.

PJ47 Over a year ago

Also not sure how you quickly created a working example. If it is an online tool it will be very helpful if you share the URL. Thank You.

Bhavya Over a year ago

@PJ47 I have not used any online tool :-)

Bhavya Over a year ago

@PJ47 You can refer to this official documentation, to know more about must/should/filter clause --> elastic.co/guide/en/elasticsearch/reference/current/…

Bhavya Over a year ago

@PJ47 I just searched right now, and I found this tool which you can use to convert SQL query to elasticsearch query sqltoelasticsearch.azurewebsites.net

|

Collectives™ on Stack Overflow

Elastic search query for SQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related