6

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": "Nam@Test.com",
      "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": "Nam@Test.COm",
      "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": "Nam@Test.COm",
      "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"
      }
    ]
  }
}
Adam Lambert
  • 1,311
  • 3
  • 24
  • 45
  • can you share your mapping and some sample docs – Amit Mar 22 '20 at 02:24
  • @OpsterElasticsearchNinja I have shared my mapping now. – Adam Lambert Mar 22 '20 at 12:29
  • Adam which elasticsearch version you are using and looks like mapping you provide is using type and `billing_address` and `customer` are 2 types – Amit Mar 22 '20 at 13:06
  • 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 – Adam Lambert Mar 22 '20 at 13:09
  • 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. – Amit Mar 22 '20 at 13:10
  • I have provided the full _mapping now. – Adam Lambert Mar 22 '20 at 13:14
  • I have also provided example document. – Adam Lambert Mar 22 '20 at 13:17
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210100/discussion-between-opster-elasticsearch-ninja-and-adam-lambert). – Amit Mar 22 '20 at 13:17
  • @OpsterElasticsearchNinja I have added a bounty to this question. I wonder if you had any more thoughts? – Adam Lambert Mar 28 '20 at 08:46
  • Give me sometime, will go through it again in detail, – Amit Mar 28 '20 at 11:05

1 Answers1

0

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"
         }
    }
}
jaspreet chahal
  • 8,817
  • 2
  • 11
  • 29