# Query on multiple fields **URL:** \ **Category:** Elasticsearch\ **Created:** [October 11, 2012, 7:39am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313 "2012-10-11T07:39:18Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![hemant\_pahilwani](https://avatars.discourse-cdn.com/v4/letter/h/f08c70/32.png) [@hemant\_pahilwani](https://discuss.elastic.co/u/hemant_pahilwani)\ **Post date:** [October 11, 2012, 7:39am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/1 "2012-10-11T07:39:18Z")
I am new to elastic search and am currently migrating the lucene queries that i have. Following is the setup for index properties : index : analysis : analyzer : myStandard : tokenizer : nGram filter : [standard, lowercase, stop] "user" : { "\_source" : { "enabled" : true }, "properties" : { "userId" : { "type" : "string", "index" : "analyzed", "analyzer" : "myStandard" }, "firstName" : { "type" : "string", "index" : "analyzed", "analyzer" : "myStandard" }, "lastName" : { "type" : "string", "index" : "analyzed", "analyzer" : "myStandard" }, "email" : { "type" : "string", "index" : "not\_analyzed"} } } Situation is that user can enter any text in search box to search for user and search needs to be performed against firstName and lastName. It would be a OR query with both prefix and suffix wild card. Like _firstName_ OR _lastName_ exact matches of firstName and lastName have to be displayed top. What would be best way to acheive this? bool query?? also is nGram the correct tokenizer in these situations? -- ---
**Author:** ![hemant\_pahilwani](https://avatars.discourse-cdn.com/v4/letter/h/f08c70/32.png) [@hemant\_pahilwani](https://discuss.elastic.co/u/hemant_pahilwani)\ **Post date:** [October 11, 2012, 8:39am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/2 "2012-10-11T08:39:55Z")
Okay i figured out query for my situation but the result coming back has few records that i wasnt expecting _data in index:_ "1", "John", "Doe", "[johndoe@somecompany.com](mailto:johndoe@somecompany.com)" "2", "Johny", "Doey", "[johndoe34@somecompany.com](mailto:johndoe34@somecompany.com)" "3", "Johnathan", "Chang", "johndoe56@ [somecompany.com](http://somecompany.com)" "4", "longjohn", "silver", "johndoe76@somecompany com" "5", "dijohn", "Doera", "johndoe12@ [somecompany.com](http://somecompany.com)" "6", "papajohn", "Do", "[johndoe09@somecompany.com](mailto:johndoe09@somecompany.com)" "7", "ojohnyyy", "Dey", "[johndoe87@somecompany.com](mailto:johndoe87@somecompany.com)" "8", "jerry", "seinfield", "[someemails@somecompany.com](mailto:someemails@somecompany.com)" "9", "kramer", "dicosta", "[someemailagain@somecompany.com](mailto:someemailagain@somecompany.com)" \*Following query is returning all the records: \* curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' --data-binary '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"_john doe_","use\_dis\_max":true}}}' Records with id's from 1-7 are correct and are expected but 8 & 9 are also being returned which dont really match any of the values passed in query. Any ideas on how to ignore/remove the last 2 records from search result? On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > I am new to Elasticsearch and am currently migrating the lucene queries > that i have. Following is the setup for index properties : > > index : > analysis : > analyzer : > myStandard : > tokenizer : nGram > filter : [standard, lowercase, stop] > > "user" : { > "\_source" : { "enabled" : true }, > "properties" : { > "userId" : { "type" : "string", "index" : "analyzed", > "analyzer" : "myStandard" }, > "firstName" : { "type" : "string", "index" : "analyzed", > "analyzer" : "myStandard" }, > "lastName" : { "type" : "string", "index" : "analyzed", > "analyzer" : "myStandard" }, > "email" : { "type" : "string", "index" : "not\_analyzed"} > } > } > > Situation is that user can enter any text in search box to search for user > and search needs to be performed against firstName and lastName. It would > be a OR query with both prefix and suffix wild card. Like _firstName_ OR > _lastName_ > exact matches of firstName and lastName have to be displayed top. What > would be best way to acheive this? bool query?? also is nGram the correct > tokenizer in these situations? -- ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 11, 2012, 8:54am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/3 "2012-10-11T08:54:56Z")
Looks like you are using a default ngram filter. By default, ngrams have min size 1 and max size 2. So, I suppose that seinfield is tokenized as "s, se, e, ei, ... d". When you search on john doe, it's also tokenized with the same analyzer. And you have a "d" in "doe". So d match d and you get your document back. Correct me if I'm wrong. David. Le 11 octobre 2012 à 10:39, hemantp [hemant.pahilwani@gmail.com](mailto:hemant.pahilwani@gmail.com) a écrit : > Okay i figured out query for my situation but the result coming back has few > records that i wasnt expecting > > data in index: > > "1", "John", "Doe", "[johndoe@somecompany.com](mailto:johndoe@somecompany.com)" > > "2", "Johny", "Doey", "[johndoe34@somecompany.com](mailto:johndoe34@somecompany.com)" > > "3", "Johnathan", "Chang", "johndoe56@ [somecompany.com](http://somecompany.com)" > > "4", "longjohn", "silver", "johndoe76@somecompany com" > > "5", "dijohn", "Doera", "johndoe12@ [somecompany.com](http://somecompany.com)" > > "6", "papajohn", "Do", "[johndoe09@somecompany.com](mailto:johndoe09@somecompany.com)" > > "7", "ojohnyyy", "Dey", "[johndoe87@somecompany.com](mailto:johndoe87@somecompany.com)" > > "8", "jerry", "seinfield", "[someemails@somecompany.com](mailto:someemails@somecompany.com)" > > "9", "kramer", "dicosta", "[someemailagain@somecompany.com](mailto:someemailagain@somecompany.com)" > > Following query is returning all the records: > > curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' --data-binary > '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"john > doe","use\_dis\_max":true}}}' > > Records with id's from 1-7 are correct and are expected but 8 & 9 are also > being returned which dont really match any of the values passed in query. > > Any ideas on how to ignore/remove the last 2 records from search result? > > On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > > > > I am new to Elasticsearch and am currently migrating the lucene > > > queries that i have. Following is the setup for index properties : > > > > index : > > analysis : > > analyzer : > > myStandard : > > tokenizer : nGram > > filter : [standard, lowercase, stop] > > > > "user" : { > > "\_source" : { "enabled" : true }, > > "properties" : { > > "userId" : { "type" : "string", "index" : "analyzed", > > "analyzer" : "myStandard" }, > > "firstName" : { "type" : "string", "index" : "analyzed", > > "analyzer" : "myStandard" }, > > "lastName" : { "type" : "string", "index" : "analyzed", > > "analyzer" : "myStandard" }, > > "email" : { "type" : "string", "index" : "not\_analyzed"} > > } > > } > > > > Situation is that user can enter any text in search box to search for > > user and search needs to be performed against firstName and lastName. It > > would be a OR query with both prefix and suffix wild card. Like _firstName_ > > OR _lastName_ > > exact matches of firstName and lastName have to be displayed top. What > > would be best way to acheive this? bool query?? also is nGram the correct > > tokenizer in these situations? > > > > > > > -- -- David Pilato [http://www.scrutmydocs.org/](http://www.scrutmydocs.org/) [http://dev.david.pilato.fr/](http://dev.david.pilato.fr/) Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [October 11, 2012, 9:51am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/4 "2012-10-11T09:51:13Z")
Hiya > curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' > --data-binary > '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"john doe","use\_dis\_max":true}}}' > > Records with id's from 1-7 are correct and are expected but 8 & 9 are > also being returned which dont really match any of the values passed > in query. You're using ngrams on all of your fields, so "john" is becoming a search for j,o,h,n,jo,oh,hn,joh,ohn,john And the default operator for query string is OR, which means that any document with any one of those letters is being returned. clint > Any ideas on how to ignore/remove the last 2 records from search > result? > > On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > I am new to Elasticsearch and am currently migrating the > lucene queries that i have. Following is the setup for index > properties : > > ``` > index : > analysis : > analyzer : > myStandard : > tokenizer : nGram > filter : [standard, lowercase, stop] > > > "user" : { > "_source" : { "enabled" : true }, > "properties" : { > "userId" : { "type" : "string", "index" : > "analyzed", "analyzer" : "myStandard" }, > "firstName" : { "type" : "string", "index" : > "analyzed", "analyzer" : "myStandard" }, > "lastName" : { "type" : "string", "index" : > "analyzed", "analyzer" : "myStandard" }, > "email" : { "type" : "string", "index" : > "not_analyzed"} > } > } > > > Situation is that user can enter any text in search box to > search for user and search needs to be performed against > firstName and lastName. It would be a OR query with both > prefix and suffix wild card. Like *firstName* OR *lastName* > exact matches of firstName and lastName have to be displayed > top. What would be best way to acheive this? bool query?? > also is nGram the correct tokenizer in these situations? > > ``` > > -- -- ---
**Author:** ![hemant\_pahilwani](https://avatars.discourse-cdn.com/v4/letter/h/f08c70/32.png) [@hemant\_pahilwani](https://discuss.elastic.co/u/hemant_pahilwani)\ **Post date:** [October 11, 2012, 6:22pm UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/5 "2012-10-11T18:22:21Z")
Thanks Clint and David. I tried changing min size and max size but the results are not what i expected. May be i need to change the analyzer or way i am querying. What would be the best way to query? is query\_string query appropriate for my situation?? On Thursday, October 11, 2012 2:51:33 AM UTC-7, Clinton Gormley wrote: > Hiya > > > curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' > > --data-binary > > '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"john > doe","use\_dis\_max":true}}}' > > > Records with id's from 1-7 are correct and are expected but 8 & 9 are > > also being returned which dont really match any of the values passed > > in query. > > You're using ngrams on all of your fields, so "john" is becoming a > search for j,o,h,n,jo,oh,hn,joh,ohn,john > > And the default operator for query string is OR, which means that any > document with any one of those letters is being returned. > > clint > > > Any ideas on how to ignore/remove the last 2 records from search > > result? > > > > On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > > I am new to Elasticsearch and am currently migrating the > > lucene queries that i have. Following is the setup for index > > properties : > > > > ``` > > index : > > analysis : > > analyzer : > > myStandard : > > tokenizer : nGram > > filter : [standard, lowercase, stop] > > > > > > "user" : { > > "_source" : { "enabled" : true }, > > "properties" : { > > "userId" : { "type" : "string", "index" : > > "analyzed", "analyzer" : "myStandard" }, > > "firstName" : { "type" : "string", "index" : > > "analyzed", "analyzer" : "myStandard" }, > > "lastName" : { "type" : "string", "index" : > > "analyzed", "analyzer" : "myStandard" }, > > "email" : { "type" : "string", "index" : > > "not_analyzed"} > > } > > } > > > > > > Situation is that user can enter any text in search box to > > search for user and search needs to be performed against > > firstName and lastName. It would be a OR query with both > > prefix and suffix wild card. Like *firstName* OR *lastName* > > exact matches of firstName and lastName have to be displayed > > top. What would be best way to acheive this? bool query?? > > also is nGram the correct tokenizer in these situations? > > > > ``` > > > > -- -- ---
**Author:** ![hemant\_pahilwani](https://avatars.discourse-cdn.com/v4/letter/h/f08c70/32.png) [@hemant\_pahilwani](https://discuss.elastic.co/u/hemant_pahilwani)\ **Post date:** [October 11, 2012, 8:12pm UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/6 "2012-10-11T20:12:59Z")
Fuzzy search seems to be close to what is required but i am not able to find a way to do fuzzy query on multiple fields. Is it possible?? I tried doing through bool query but it didnt give me any results. Any ideas on how to do fuzzy (not fuzzy like this) query on multiple fields? On Thursday, October 11, 2012 11:22:21 AM UTC-7, hemantp wrote: > Thanks Clint and David. I tried changing min size and max size but the > results are not what i expected. May be i need to change the analyzer or > way i am querying. What would be the best way to query? is query\_string > query appropriate for my situation?? > > On Thursday, October 11, 2012 2:51:33 AM UTC-7, Clinton Gormley wrote: > > > Hiya > > > > > curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' > > > --data-binary > > > > '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"john > > doe","use\_dis\_max":true}}}' > > > > > Records with id's from 1-7 are correct and are expected but 8 & 9 are > > > also being returned which dont really match any of the values passed > > > in query. > > > > You're using ngrams on all of your fields, so "john" is becoming a > > search for j,o,h,n,jo,oh,hn,joh,ohn,john > > > > And the default operator for query string is OR, which means that any > > document with any one of those letters is being returned. > > > > clint > > > > > Any ideas on how to ignore/remove the last 2 records from search > > > result? > > > > > > On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > > > I am new to Elasticsearch and am currently migrating the > > > lucene queries that i have. Following is the setup for index > > > properties : > > > > > > ``` > > > index : > > > analysis : > > > analyzer : > > > myStandard : > > > tokenizer : nGram > > > filter : [standard, lowercase, stop] > > > > > > > > > "user" : { > > > "_source" : { "enabled" : true }, > > > "properties" : { > > > "userId" : { "type" : "string", "index" : > > > "analyzed", "analyzer" : "myStandard" }, > > > "firstName" : { "type" : "string", "index" : > > > "analyzed", "analyzer" : "myStandard" }, > > > "lastName" : { "type" : "string", "index" : > > > "analyzed", "analyzer" : "myStandard" }, > > > "email" : { "type" : "string", "index" : > > > "not_analyzed"} > > > } > > > } > > > > > > > > > Situation is that user can enter any text in search box to > > > search for user and search needs to be performed against > > > firstName and lastName. It would be a OR query with both > > > prefix and suffix wild card. Like *firstName* OR *lastName* > > > exact matches of firstName and lastName have to be displayed > > > top. What would be best way to acheive this? bool query?? > > > also is nGram the correct tokenizer in these situations? > > > > > > ``` > > > > > > -- -- ---
**Author:** ![Chris\_Male](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chris_male/32/2607_2.png) [@Chris\_Male](https://discuss.elastic.co/u/Chris_Male)\ **Post date:** [October 11, 2012, 10:59pm UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/7 "2012-10-11T22:59:32Z")
One thing to consider about Fuzzy search is that it is extremely expensive performance wise. Having the leading wildcard is going to prevent any real optimization as well. I wonder what the impact of changing the default search operator to AND would be. For 'john' that would mean the matched document would need to have 'j', 'o', 'h', 'n' 'jo', 'oh', 'hn'. Going back to your list of matched documents, this would still match the first 7 but wouldn't match 8 and 9, giving you the behaviour you want. On Friday, October 12, 2012 9:12:59 AM UTC+13, hemantp wrote: > Fuzzy search seems to be close to what is required but i am not able to > find a way to do fuzzy query on multiple fields. Is it possible?? I tried > doing through bool query but it didnt give me any results. Any ideas on > how to do fuzzy (not fuzzy like this) query on multiple fields? > > On Thursday, October 11, 2012 11:22:21 AM UTC-7, hemantp wrote: > > > Thanks Clint and David. I tried changing min size and max size but the > > results are not what i expected. May be i need to change the analyzer or > > way i am querying. What would be the best way to query? is query\_string > > query appropriate for my situation?? > > > > On Thursday, October 11, 2012 2:51:33 AM UTC-7, Clinton Gormley wrote: > > > > > Hiya > > > > > > > curl -XGET '[http://localhost:9200/index/\_search?&pretty=true](http://localhost:9200/index/_search?&pretty=true)' > > > > --data-binary > > > > > > '{"query":{"query\_string":{"fields":["firstName","lastName"],"query":"john > > > doe","use\_dis\_max":true}}}' > > > > > > > Records with id's from 1-7 are correct and are expected but 8 & 9 are > > > > also being returned which dont really match any of the values passed > > > > in query. > > > > > > You're using ngrams on all of your fields, so "john" is becoming a > > > search for j,o,h,n,jo,oh,hn,joh,ohn,john > > > > > > And the default operator for query string is OR, which means that any > > > document with any one of those letters is being returned. > > > > > > clint > > > > > > > Any ideas on how to ignore/remove the last 2 records from search > > > > result? > > > > > > > > On Thursday, October 11, 2012 12:39:18 AM UTC-7, hemantp wrote: > > > > I am new to Elasticsearch and am currently migrating the > > > > lucene queries that i have. Following is the setup for index > > > > properties : > > > > > > > > ``` > > > > index : > > > > analysis : > > > > analyzer : > > > > myStandard : > > > > tokenizer : nGram > > > > filter : [standard, lowercase, stop] > > > > > > > > > > > > "user" : { > > > > "_source" : { "enabled" : true }, > > > > "properties" : { > > > > "userId" : { "type" : "string", "index" : > > > > "analyzed", "analyzer" : "myStandard" }, > > > > "firstName" : { "type" : "string", "index" : > > > > "analyzed", "analyzer" : "myStandard" }, > > > > "lastName" : { "type" : "string", "index" : > > > > "analyzed", "analyzer" : "myStandard" }, > > > > "email" : { "type" : "string", "index" : > > > > "not_analyzed"} > > > > } > > > > } > > > > > > > > > > > > Situation is that user can enter any text in search box to > > > > search for user and search needs to be performed against > > > > firstName and lastName. It would be a OR query with both > > > > prefix and suffix wild card. Like *firstName* OR *lastName* > > > > exact matches of firstName and lastName have to be displayed > > > > top. What would be best way to acheive this? bool query?? > > > > also is nGram the correct tokenizer in these situations? > > > > > > > > ``` > > > > > > > > -- -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:09am UTC](https://discuss.elastic.co/t/query-on-multiple-fields/9313/8 "2017-07-06T03:09:12Z")