AND and IN operator in Elasticsearch query-CodePudding

I am trying to achieve the following sql in elastic search:

SELECT * FROM table WHERE id1 = "x1" AND id2 ="relationship" AND products IN ("prod1","prod2") AND id3 = "food"

Products can have one value or multiple values. It will be parameterized.

How can I achieve this in Elasticsearch query.

I tried the following but facing error:

GET /indexname/seaach
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "id1": "x1"
          }
        },
        {
          "match": {
            "id2": "relationship"
          }
        },
        {
          "match": {
            "Products": ["prod1","prod2"]
          }
        },
        {
          "match": {
            "id3": "food"
          }
        }
      ]
    }
  }
}

CodePudding user response：

Try this with Terms Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "id1": "x1"
          }
        },
        {
          "match": {
            "id2": "relationship"
          }
        },
        {
          "terms": {
            "products": [
              "prod1",
              "prod2"
            ]
          }
        },
        {
          "match": {
            "id3": "food"
          }
        }
      ]
    }
  }
}