venkatesh p venkatesh p - 3 months ago 10
SQL Question

Elasticsearch match combos of two fields

How can I get this simple SQL query running on Elasticsearch?

SELECT * FROM [mytype] where (id=123 and cid = classroomIdA) or
(id=234 and cid = classroomIdB) or (id=345 and cid = classroomIdC)


I'm really having troubles with its syntax, multi-match queries doesn't work in my case. What type of query should I use?

Val Val
Answer

The right way to do it is to combine bool/should (for the outer OR conditions) and bool/filter (for the inner AND conditions) together.

POST mytype/_search
{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "filter": [
              {
                "term": {
                  "id": 123
                }
              },
              {
                "term": {
                  "cid": "classroomIdA"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              {
                "term": {
                  "id": 234
                }
              },
              {
                "term": {
                  "cid": "classroomIdB"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              {
                "term": {
                  "id": 345
                }
              },
              {
                "term": {
                  "cid": "classroomIdC"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

UPDATE

The equivalent ES 1.7 query would be (just replace bool/filter by bool/must):

POST mytype/_search
{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "id": 123
                }
              },
              {
                "term": {
                  "cid": "classroomIdA"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "id": 234
                }
              },
              {
                "term": {
                  "cid": "classroomIdB"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "id": 345
                }
              },
              {
                "term": {
                  "cid": "classroomIdC"
                }
              }
            ]
          }
        }
      ]
    }
  }
}