How can I write an "or" query in Elasticsearch?

Question

I have this SQL:

select *
from users
where company_id = null
  or company_id = 123

How can I write a similar query in Elasticsearch? I want the query to return all candidates who have a company_id that is nil or who have a company_id that matches the current user.

I have tried this with should:

query: {
  bool: {
    should: [
      {
        bool: {
          must_not: {
            exists: {
              field: "company_id"
            }
          }
        }
      },
      {
        term: {
          company_id: 123
        }
      }
    ]
  }
}

This returns users whose company_id is nil.

I've tried this with regex also but it returns users who have a company_id.

query: {
  bool: {
    should: [
      {
        regexp: {
          comapny_id: ".+"
        }
      },
      {
        term: {
          company_id: {
            value: 123
          }
        }
      }
    ]
  }
}

Thank you.

2nd one, that query only returns the user whose company_is is nil — yash upadhyay, Jul 06 '21 at 18:37
Does this [SO Post](https://stackoverflow.com/questions/43132240/elastic-search-query-to-check-for-null-values-in-2-fields) help? I am not that familiar with elastic search syntax — engineersmnky, Jul 06 '21 at 18:51
Your first attempt should work. What is the data type of your `company_id` field? When you just use the `term` query are you able to find a candidate with an ID of `123`? — cschroed, Jul 06 '21 at 19:34
company_id data type in elasticsearch was TEXT, because on active record it's datatype i UUID. no when i used only term that doesn't retuen candidate, i think my problem wih this term query. — yash upadhyay, Jul 07 '21 at 04:54

score 1 · Accepted Answer · answered Jul 07 '21 at 12:10

1

In the comments you mentioned that the company_id is really a UUID and the type is currently text. You should try to change your company_id to a keyword field. You will need to reindex after making this change.

Elasticsearch doesn't recommend using a term query against a text field:

Avoid using the term query for text fields.

By default, Elasticsearch changes the values of text fields as part of analysis. This can make finding exact matches for text field values difficult.

To search text field values, use the match query instead.

Term Query

Their suggestion here is to use match, but I don't think that you'll need to support partial matching against randomly generated UUID values, so I think you should define your field as a keyword instead.

answered Jul 07 '21 at 12:10

cschroed

6,304
6
42
56

worked thank you so much only when i change company_id type from text to keyword. – yash upadhyay Jul 07 '21 at 20:46
Can i run must with first one query, i want to satisfy both condition with and operator – yash upadhyay Aug 24 '21 at 08:20
Yes, if you're saying you want to check that a `company_id` exists you'd need to change the `must_not` to `must`. If you're saying you want to require both filters to be `true` you'd need to change the `should` to `must`. – cschroed Aug 24 '21 at 12:07

How can I write an "or" query in Elasticsearch?

1 Answers1