Query search based on condition (Elasticsearch v6.84) - please advice

[Chetan_Ramaiah]

Hi,

Searching based on multiple fields such as name, country, company, 30days_flag, 60days_flag, 90days_flag. I am trying to fetch all companies which took part in a survey from the last 30, 60 and 90 days from the specified countries.

If companies have taken part in a survey with below range

(> 90 days) then we store 'Y' for 90days_flag and 'N' for 30days_flag and 60days_flag field. so user will not get result if they search for 30 days or 60 days.

(< 90 days and > 60 days) then we store 'Y' for 90days_flag and 60days_flag (reason if user searches for 90 days then user is searching for a range between 1 to 90 days). And 'N' for 30days_flag field. i.e., user will not get result if they search for 30 days.

(< 90 days and < 60 days and > 30 days) then we store 'Y' for 90days_flag, 60days_flag and 30days_flag (reason if user searches for 90 days then user is searching for a range between 1 to 90 days).

Below is the JSON:

       {
      "bool" : {
        "must" : [
          {
            "multi_match" : {
              "query" : "dan",
              "fields" : [
                "email^1.0",
                "name^1.0"
              ],
              "type" : "best_fields",
              "operator" : "OR",
              "analyzer" : "standard",
              "slop" : 0,
              "fuzziness" : "AUTO",
              "prefix_length" : 0,
              "max_expansions" : 50,
              "zero_terms_query" : "NONE",
              "auto_generate_synonyms_phrase_query" : true,
              "fuzzy_transpositions" : true,
              "boost" : 1.0
            }
          }
        ],
        "filter" : [
          {
            "terms" : {
              "comp_name.keyword" : [
                "Coodo"
              ],
              "boost" : 1.0
            }
          },
          {
            "terms" : {
              "countries" : [
                "Switzerland"
              ],
              "boost" : 1.0
            }
          },
          {
            "multi_match" : {
              "query" : "Y",
              "fields" : [
                "ninty_day_flag^1.0",
                "sixty_day_flag^1.0",
                "thirty_day_flag^1.0"
              ],
              "type" : "best_fields",
              "operator" : "OR",
              "slop" : 0,
              "prefix_length" : 0,
              "max_expansions" : 50,
              "zero_terms_query" : "NONE",
              "auto_generate_synonyms_phrase_query" : true,
              "fuzzy_transpositions" : true,
              "boost" : 1.0
            }
          }
        ],
        "adjust_pure_negative" : true,
        "boost" : 1.0
      }
    }

Above json works if all 3 flags are 'Y' and fails to return data if any of the field is 'N'. But, our requirement is to get result even if any of the flag is 'N'.

Let's say 90days_flag = 'Y', 60days_flag = 'Y' and 30days_flag = 'N', I should be able to get data for those 90 and 60 days. and one more scenario, let's say 90days_flag = 'Y', 60days_flag = 'N' and 30days_flag = 'N', then I should be able to get data for those 90 days only.

Please advice.

Thanks in advance!

[Emanuil (Emanuil Tolev)]

Is ninty_day_flag from filter.multi_match.fields a copy/paste typo or an actual spelling error in the query? (missing e)

[Chetan_Ramaiah]

Hi Emanuil, it's actual spelling error but it's used consistently through out the application

[Emanuil (Emanuil Tolev)]

Ah, one of those situations, I sympathise .

Apologies for going straight for another question, I just had the idea. Any reason why you don't store when a company took the survey as a date and time? Then you can simply use a range query rather than this set of flags. You can even do convenient date math with that query. You can express "greater than 90 days", "between 60 and 90", "between 30 and 60" and "under 30" with it and get rid of the flags entirely.

If that is not possible, take a look at this warning. You're using "type" : "best_fields" in the multi_match filter which means that the match is evaluated on a per field basis - likely not what you want, as the warning says. For a quick fix, try changing the multi_match filter to use cross_fields instead of best_fields. I still think a Range query would be superior if you can start recording the survey date.