Combine OR, AND and IN operators in Elasticsearch query

Question

I am very new to the elasticsearch and I need to prepare the query which combines OR, AND and IN operators. What I want to achieve is having something like this in SQL:

SELECT * FROM tableWHERE (field_1 = 'foo' AND field_2 IN(1,2,3) ) OR ('field_1 = 'bar' AND field_2 IN(2, 3, 4) );

I am using elastic with PHP and have started with something like this:

                'query' => [
                    'bool' => [
                        'should' => [
                            [
                                'match' => [
                                    'field_1' => 'foo',
                                ],
                            ],
                            [
                                'match' => [
                                    'field_1' => 'bar',
                                ],
                            ],
                        ],

                    ],
                ],

However, I cannot add query parameters to achive my desired result. Can you please help me with that?

Answer 1

You need to combine bool/must/should query clauses. For IN operator you can use terms query in elasticsearch

Try out this below query

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "match": {
                  "field_1": "foo"
                }
              },
              {
                "terms": {
                  "field_2": [
                    1,
                    2,
                    3
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "match": {
                  "field_1": "bar"
                }
              },
              {
                "terms": {
                  "field_2": [
                    2,
                    3,
                    4
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}