Elasticsearch default_operator "AND" not working as expected

Question

I have setup my query string search with the "AND" default operator. My query is as follows:

{
    "query": {
        "query_string" : {
            "query" : "Adam KT2 7AJ",
            "default_operator" : "AND"
         }
    }
}

I would expect this to provide the same result as the below... but this does not seem the case.

{
    "query": {
        "query_string" : {
            "query" : "Adam AND KT2 AND 7AJ",
            "default_operator" : "OR"
         }
    }
}

While I see the default_operator is making a difference to my search results, it does not work as I would like.

For example:

QUERY1:

• Adam AND KT2 AND 7AJ, default_operator: or
• results: 1 (as expected)
• [1] Name: Adam Lambert, Postcode KT2 7AJ

QUERY2:

• Adam KT2 7AJ, default_operator: and
• results: 0
• (Not expected. This should produce the same results as above? It seems and as the default_operator produces different results to AND in the query)

QUERY3:

• KT2 7AJ, default_operator: and
• results: 2
• (As expected, only matches both parts of postcode)
• [1] Name: a, Postcode KT2 7AJ
• [2] Name: b, Postcode KT2 7AJ

QUERY4:

• KT2 7AJ, default_operator: or
• results: 5
• (As expected, parial match on various postcodes)
• [1] Name: a, Postcode KT2 7AJ
• [2] Name: b, Postcode KT2 7AJ
• [3] Name: c, Postcode N7 7AJ
• [4] Name: d, Postcode KT2 0DJ
• [5] Name: e, Postcode KT2 0PQ

EDIT

Here is my mapping. You will note that there is a post_code field in the billing address but there are name fields attached to both the shipping_address and the customer.

When I run the query as Adam AND KT2 7AJ, we are getting a result for where the customer.name:Adam AND billing_address.post_code:KT2 7AJ, which is what I am expecting.

If we remove the AND, we get 0 results.

{
    "t1_orders_1584882055": {
        "mappings": {
            "properties": {
                "__class_name": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "billing_address": {
                    "properties": {
                        "address1": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "address2": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "address3": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "company": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "country_code": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "created_at": {
                            "type": "date",
                            "format": "date_time"
                        },
                        "customer_id": {
                            "type": "long"
                        },
                        "email": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "id": {
                            "type": "keyword"
                        },
                        "name": {
                            "type": "text",
                            "analyzer": "edge_ngram_analyzer"
                        },
                        "phone": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "post_code": {
                            "type": "text",
                            "fields": {
                                "text": {
                                    "type": "text"
                                }
                            },
                            "analyzer": "no_space_analyzer"
                        },
                        "redacted": {
                            "type": "boolean"
                        },
                        "region": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "town": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "updated_at": {
                            "type": "date",
                            "format": "date_time"
                        }
                    }
                },
                "billing_address_id": {
                    "type": "long"
                },
                "channel": {
                    "properties": {
                        "created_at": {
                            "type": "date"
                        },
                        "id": {
                            "type": "long"
                        },
                        "identifier": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "name": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "updated_at": {
                            "type": "date"
                        }
                    }
                },
                "channel_id": {
                    "type": "long"
                },
                "created_at": {
                    "type": "date",
                    "format": "date_time"
                },
                "customer": {
                    "properties": {
                        "created_at": {
                            "type": "date",
                            "format": "date_time"
                        },
                        "email": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "id": {
                            "type": "keyword"
                        },
                        "name": {
                            "type": "text",
                            "analyzer": "edge_ngram_analyzer"
                        },
                        "redacted": {
                            "type": "boolean"
                        },
                        "updated_at": {
                            "type": "date",
                            "format": "date_time"
                        }
                    }
                },
                "customer_id": {
                    "type": "long"
                },
                "id": {
                    "type": "keyword"
                },
                "name": {
                    "type": "text",
                    "analyzer": "edge_ngram_analyzer"
                },
                "order_id": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "order_items": {
                    "properties": {
                        "created_at": {
                            "type": "date"
                        },
                        "id": {
                            "type": "long"
                        },
                        "order_id": {
                            "type": "long"
                        },
                        "qty": {
                            "type": "long"
                        },
                        "sku": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "updated_at": {
                            "type": "date"
                        }
                    }
                },
                "received_at": {
                    "type": "date",
                    "format": "date_time"
                },
                "reference_number": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "ship_by": {
                    "type": "date"
                },
                "shipping_address": {
                    "properties": {
                        "address1": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "address2": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "address3": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "company": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "country_code": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "created_at": {
                            "type": "date",
                            "format": "date_time"
                        },
                        "customer_id": {
                            "type": "long"
                        },
                        "email": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "id": {
                            "type": "keyword"
                        },
                        "name": {
                            "type": "text",
                            "analyzer": "edge_ngram_analyzer"
                        },
                        "phone": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "post_code": {
                            "type": "text",
                            "fields": {
                                "text": {
                                    "type": "text"
                                }
                            },
                            "analyzer": "no_space_analyzer"
                        },
                        "redacted": {
                            "type": "boolean"
                        },
                        "region": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "town": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "updated_at": {
                            "type": "date",
                            "format": "date_time"
                        }
                    }
                },
                "shipping_address_id": {
                    "type": "long"
                },
                "status": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "updated_at": {
                    "type": "date",
                    "format": "date_time"
                }
            }
        }
    }
}

Example Document:

{
  "_source": {
    "id": 6,
    "channel_id": 1,
    "customer_id": 1,
    "shipping_address_id": 11,
    "billing_address_id": 12,
    "order_id": "1234",
    "reference_number": "1234",
    "status": "open",
    "received_at": "2020-02-24T18:03:21.000000Z",
    "ship_by": "2020-02-24T18:03:21.000000Z",
    "completed_at": null,
    "created_at": "2020-03-18T16:17:28.000000Z",
    "updated_at": "2020-03-18T16:17:28.000000Z",
    "channel": {
      "id": 1,
      "name": "Amazon",
      "identifier": "The",
      "created_at": "2020-03-18T16:17:28.000000Z",
      "updated_at": "2020-03-18T16:17:28.000000Z"
    },
    "customer": {
      "id": 1,
      "name": "Nam Test",
      "email": "[email protected]",
      "redacted": false,
      "created_at": "2020-03-18T16:17:28.000000Z",
      "updated_at": "2020-03-18T16:17:28.000000Z"
    },
    "shipping_address": {
      "id": 11,
      "customer_id": 1,
      "name": "Nam Test",
      "email": "[email protected]",
      "company": "",
      "address1": "44",
      "address2": "AD CLOSE",
      "address3": "",
      "town": "KINGSTON UPON THAMES",
      "region": "",
      "post_code": "KT2 7AJ",
      "country_code": null,
      "phone": "213223123",
      "redacted": false,
      "created_at": "2020-03-18T16:17:28.000000Z",
      "updated_at": "2020-03-18T16:17:28.000000Z"
    },
    "billing_address": {
      "id": 12,
      "customer_id": 1,
      "name": "Nam Test",
      "email": "[email protected]",
      "company": "",
      "address1": "44",
      "address2": "AD CLOSE",
      "address3": "",
      "town": "KINGSTON UPON THAMES",
      "region": "",
      "post_code": "KT2 7AJ",
      "country_code": null,
      "phone": "213223123",
      "redacted": false,
      "created_at": "2020-03-18T16:17:28.000000Z",
      "updated_at": "2020-03-18T16:17:28.000000Z"
    },
    "order_items": [
      {
        "id": 6,
        "order_id": 6,
        "sku": "10-2-sk-ue",
        "qty": 1,
        "created_at": "2020-03-18T16:17:28.000000Z",
        "updated_at": "2020-03-18T16:17:28.000000Z"
      }
    ]
  }
}

Magnific

can you share your mapping and some sample docs – Hoot

@OpsterElasticsearchNinja I have shared my mapping now. – Magnific

Adam which elasticsearch version you are using and looks like mapping you provide is using type and billing_address and customer are 2 types – Hoot

Billing address and customer are both nested. We are actually using a php library so some of the setup on that is automated, but we can still provide modifications. We are using ES 7.6 – Magnific

Thanks for the info, I tried creating this mapping in 7.6 and got issues, can you get the proper mapping by hitting _mapping and share sample documents in JSON format so that its easy to test in my local env. – Hoot

I have provided the full _mapping now. – Magnific

I have also provided example document. – Magnific

Let us continue this discussion in chat. – Hoot

@OpsterElasticsearchNinja I have added a bounty to this question. I wonder if you had any more thoughts? – Magnific

Give me sometime, will go through it again in detail, – Hoot

Answer 1

From elastic search version 6.x split by whitespace is not done anymore.

Explanation: Taking two fields Name and PostalCode

1. Adam AND KT2 AND 7AJ, default_operator: or
Query translates to :-
(Name:Adam OR PostalCode:Adam) OR (Name:KT2 OR PostalCode:KT2) OR (Name:7AJ OR PostalCode:7AJ)
Text is split on operators(AND,OR) and each split is passed to respective fields So it returns all the documents which have name as Adam and Postal code containg 7AJ or KT2 (tokens are matched)

2. Adam AND KT2 AND 7AJ, default_operator: and
Query translates to :-
(Name:Adam OR PostalCode:Adam) AND (Name:KT2 OR PostalCode:KT2) AND (Name:7AJ OR PostalCode:7AJ)

3. Adam KT2 7AJ, default_operator: and
Query translates to :-
(Name:(Adam AND KT2 AND 7AJ) OR PostalCode:(Adam AND KT2 AND 7AJ))
text is not split on whitespace(this was done before version 6) and is considered to be a block of text. So only document which has all three tokens in any field is returned

4. KT2 7AJ, default_operator: and
Same as point 2 document which has both tokens in any field is returned.

5. KT2 7AJ, default_operator: or
Same as point 2 document with any of token(OR) in a field is returned

This link has a similar clarification on it by elastic search team on github

If you want to keep on using existing structure you can club similar words using brackets. Below query will split text in two tokens Adam and single block KT2 7AJ which will translate to
(Name:Adam OR PostalCode:Adam) AND (Name:(KT2 AND 7AJ) OR PostalCode:(KT2 AND 7AJ)

{
    "query": {
        "query_string" : {
            "query" : "Adam (KT2 7AJ)",
            "default_operator" : "AND"
         }
    }
}

Keeshakeeshond

anything pending in this? – Keeshakeeshond