Making filter query with elastic search

Question

I need to create the elastic search query to filter out from the logs that are already indexed into the elastic search index.

The problem statement is that list the logs of user whose email is '[email protected]' and is part of company_id '123'. The logs list change as the more filters are added. For an instance, the logs with event checkin or checkout or the users with temperature range between 28.7 - 37.8.

The equivalent mysql query is :

select * from logs
where
(
    company_id = 123 or company_id is null // company_id may be missing 
)
AND
(
    email = '[email protected]'
)
AND
(
    event = 'checkIn'
    or event = 'checkOut'
    or 
        (
            event = 'temperature'
            AND temperature >= 28.7
            AND temperature <= 37.8
        )
)

where logs is the name of index, company_id, email, event, temperature, create_date are the field(column) names.

The query I generated is :

'query' => {
    'bool' => {
        'must' => [            
            {
                'bool' => {
                    'must' => {
                        'match' => {
                            "email.keyword" => {
                                'query' => $email, 'fuzziness' => 'auto'
                            }
                        }
                    },
                    'should' => {
                        {
                            'match' => {
                                "event" => {
                                    'query' => "checkIn"
                                }
                            }
                        },
                        {
                            'match' => {
                                "event" => {
                                    'query' => "checkOut"
                                }
                            }
                        },
                        {
                            'range' => {
                                "temperature" => {
                                    "gte" => 28.7,
                                    "lte" => 37.8
                                }
                            }
                        }

                    }
                }
            {
        ],
        'should' => [
            {
                'bool' => {
                    'should' => [
                        {
                            'match' => {
                                "company_id" => [
                                    'query'     => $company_id
                                ]
                            }
                        }
                    ],
                    'must_not' => [
                        {
                            'exists' => {
                                'field' => 'company_id'
                            }
                        }
                    ]
                }
            }
        ]
    }
}

But this does not work as it should.

Any help here would be appreciated. Thanks

Answer 1

The DSL query that corresponds to your SQL query is the one below. it's slightly different than the one you have.

{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "term": {
                  "company_id": "123"
                }
              },
              {
                "bool": {
                  "must_not": {
                    "exists": {
                      "field": "company_id"
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "term": {
            "email.keyword": "[email protected]"
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "terms": {
                  "event": ["checkIn", "checkOut"]
                }
              },
              {
                "bool": {
                  "filter": [
                    {
                      "term": {
                        "event": "temperature"
                      }
                    },
                    {
                      "range": {
                        "temperature": {
                          "gte": 28.7,
                          "lte": 37.8
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}