# Search for a value across multiple fields **URL:** \ **Category:** Elasticsearch\ **Created:** [February 13, 2014, 6:00pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785 "2014-02-13T18:00:21Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Luca\_Pau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_pau/32/1796_2.png) [@Luca\_Pau](https://discuss.elastic.co/u/Luca_Pau)\ **Post date:** [February 13, 2014, 6:00pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/1 "2014-02-13T18:00:21Z")
Hello guys, I'm learning to use elasticsearch and I ran into a little problem .. My purpose is to search for a value across multiple fields and return the count of these values ​​and the distinct value. To do this I realized that I have to use the facets. This is the database schema: index: analysis: analyzer: custom\_search\_analyzer: type: custom tokenizer: standard filter : [standard, snowball, lowercase, asciifolding] custom\_index\_analyzer: type: custom tokenizer: standard filter : [standard, snowball, lowercase, asciifolding, custom\_filter] filter: custom\_filter: type: edgeNGram side: front min\_gram: 1 max\_gram: 20 { "structure": { "properties": { "name": {"type": "string", "search\_analyzer": "custom\_search\_analyzer", "index\_analyzer": "custom\_index\_analyzer"}, "locality": {"type": "string", "search\_analyzer": "custom\_search\_analyzer", "index\_analyzer": "custom\_index\_analyzer"}, "province": {"type": "string", "search\_analyzer": "custom\_search\_analyzer", "index\_analyzer": "custom\_index\_analyzer"}, "region": {"type": "string", "search\_analyzer": "custom\_search\_analyzer", "index\_analyzer": "custom\_index\_analyzer"} } } } and this is the query that I tried to use: { "query": { "bool": { "should": [ { "match": { "locality": "bolo" } }, { "match": { "region": "bolo" } }, { "match": { "name": "bolo" } } ] } }, "facets": { "region": { "query": { "term": { "region": "bolo" } } }, "locality": { "query": { "term": { "locality": "bolo" } } }, "name": { "query": { "term": { "name": "bolo" } } } } } Of all the tests I've done this is the query that is closest to my desired result, however, does not tell me the count of distinct field, I found it to count the total field. For example, the above query returns the following result: facets: { region: { \_type: query count: 0 } locality: { \_type: query count: 2 } name: { \_type: query count: 0 } } I would like to have a result like this (not so obviously written is correct, but does understand what I need): facets: { .... locality: { \_type: query "terms": [ {"term": "Bologna", "count": 1}, {"term": "Bolognano", "count": 1} ] ``` } ``` How can I do? I have already tried to use "terms" instead of "query" in the facets and put "index: not\_analyzed" in the fields of research, but is only returned if I try the exact scope, not part of it! Thanks -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9f74b8be-b17a-4c44-b738-7f0b1ea54750%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9f74b8be-b17a-4c44-b738-7f0b1ea54750%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Binh\_Ly](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@Binh\_Ly](https://discuss.elastic.co/u/Binh_Ly)\ **Post date:** [February 13, 2014, 7:51pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/2 "2014-02-13T19:51:53Z")
The closest you can probably get is to script and then sum (but you need to know the exact terms you are querying up front). It could be slow if you got lots of documents, but here is more information: [http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/modules-advanced-scripting.html](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/modules-advanced-scripting.html) For example: POST \_search { "script\_fields": { "locality\_bologna": { "script": "\_index['locality']['bologna'].tf()" } } } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5dc9cd99-b796-437d-be0e-06d78b8ee492%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5dc9cd99-b796-437d-be0e-06d78b8ee492%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Luca\_Pau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_pau/32/1796_2.png) [@Luca\_Pau](https://discuss.elastic.co/u/Luca_Pau)\ **Post date:** [February 13, 2014, 11:58pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/3 "2014-02-13T23:58:55Z")
Hello, Thank you for your response. Unfortunately this is not what I wanted. 'll Explain what I do, so maybe you can find a route different from the one I'm trying to take .. Should I build an autocomplete "smart" in the sense that for every result must be worn behind a series of information. For example: I am looking for "bologna" (my city) and I have to run a query (or more than one, I was looking for a way to make it as little as possible) where "bologna" is searched in the fields "name", "locality" and "region". If it is found, necessary to count how many structures there are in bologna (here you will understand why I was trying to use the facets). Obviously I could do this on the server side, for example with php with a loop, but the answer would be very slow and was frustrated all the speed of elasticsearch. At this point I wonder how I could do this. thanks 2014-02-13 20:51 GMT+01:00 Binh Ly [binh@hibalo.com](mailto:binh@hibalo.com): > The closest you can probably get is to script and then sum (but you need > to know the exact terms you are querying up front). It could be slow if you > got lots of documents, but here is more information: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/modules-advanced-scripting.html) > > For example: > > POST \_search > { > "script\_fields": { > "locality\_bologna": { > "script": "\_index['locality']['bologna'].tf()" > } > } > } > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/hjODe\_18d9o/unsubscribe](https://groups.google.com/d/topic/elasticsearch/hjODe_18d9o/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/5dc9cd99-b796-437d-be0e-06d78b8ee492%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5dc9cd99-b796-437d-be0e-06d78b8ee492%40googlegroups.com) > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAJNwxOO5Mqho\_FdKZ-TSdCiwj6Aanz-c6\_fMdVSGx%2BEG6jYyQw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAJNwxOO5Mqho_FdKZ-TSdCiwj6Aanz-c6_fMdVSGx%2BEG6jYyQw%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Binh\_Ly](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@Binh\_Ly](https://discuss.elastic.co/u/Binh_Ly)\ **Post date:** [February 14, 2014, 12:42pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/4 "2014-02-14T12:42:14Z")
I'm still not 100% sure I understand. Is this something that might work? { "query": { "multi\_match": { "query": "bologna", "fields": [ "locality", "region" ] } }, "script\_fields": { "bologna\_count": { "script": "\_index['locality']['bologna'].tf() + \_index['region'][' bologna'].tf()" } } } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/b4ae9668-9416-4c43-8195-652c4da4fe62%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b4ae9668-9416-4c43-8195-652c4da4fe62%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Luca\_Pau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_pau/32/1796_2.png) [@Luca\_Pau](https://discuss.elastic.co/u/Luca_Pau)\ **Post date:** [February 14, 2014, 1:29pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/5 "2014-02-14T13:29:38Z")
We're almost there! This is the result of the query that I have posted: - hits: { - total: 3 - max\_score: 4.724929 - hits: [ - { - \_index: website - \_type: structure - \_id: 7 - \_score: 4.724929 - fields: { - bologna\_count: 0 } } - { - \_index: website - \_type: structure - \_id: 8 - \_score: 4.724929 - fields: { - bologna\_count: 0 } } - { - \_index: website - \_type: structure - \_id: 6 - \_score: 4.724929 - fields: { - bologna\_count: 0 } } ] } in fact located all three records that contain "bologna" in the "locality" and "region", but bologna\_count is always 0 and replicates the result 3 times. For example: the three records they found as locality: "bologna" "bologna" "Bolognano" What I would like is that I give back as a result: (similar to this) hits: [ - { - \_index: website - \_type: structure - \_score: 4.724929 - fields: { - count: 2 locality:"bologna" } } - { - \_index: website - \_type: structure - \_score: 4.724929 - fields: { - count: 1 locality:"bolognano" } } ] so that it knows that there are 2 records with the name "bologna" and 1 as "bolognano." Thanks Il giorno venerdì 14 febbraio 2014 13:42:14 UTC+1, Binh Ly ha scritto: > I'm still not 100% sure I understand. Is this something that might work? > > { > "query": { > "multi\_match": { > "query": "bologna", > "fields": [ > "locality", > "region" > ] > } > }, > "script\_fields": { > "bologna\_count": { > "script": "\_index['locality']['bologna'].tf() + \_index['region'][' > bologna'].tf()" > } > } > } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/24d41fdb-2faf-4380-97c2-f0d56d4f0f20%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/24d41fdb-2faf-4380-97c2-f0d56d4f0f20%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Luca\_Pau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_pau/32/1796_2.png) [@Luca\_Pau](https://discuss.elastic.co/u/Luca_Pau)\ **Post date:** [February 14, 2014, 1:31pm UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/6 "2014-02-14T13:31:22Z")
Sorry, typo: "This is the result of the query _you posted_:" Il giorno venerdì 14 febbraio 2014 14:29:38 UTC+1, Luca Pau ha scritto: > We're almost there! > This is the result of the query that I have posted: > > - hits: { > - total: 3 > - max\_score: 4.724929 > - hits: [ > - { > - \_index: website > - \_type: structure > - \_id: 7 > - \_score: 4.724929 > - fields: { > - bologna\_count: 0 > } > } > > - { > - \_index: website > - \_type: structure > - \_id: 8 > - \_score: 4.724929 > - fields: { > - bologna\_count: 0 > } > } > > - { > - \_index: website > - \_type: structure > - \_id: 6 > - \_score: 4.724929 > - fields: { > - bologna\_count: 0 > } > } > ] > } > > in fact located all three records that contain "bologna" in the "locality" > and "region", but bologna\_count is always 0 and replicates the result 3 > times. > For example: > the three records they found as locality: > "bologna" > "bologna" > "Bolognano" > > What I would like is that I give back as a result: (similar to this) > hits: [ > > - { > - \_index: website > - \_type: structure > - \_score: 4.724929 > - fields: { > - count: 2 > locality:"bologna" > } > } > > - { > - \_index: website > - \_type: structure > - \_score: 4.724929 > - fields: { > - count: 1 > locality:"bolognano" > } > } > ] > > so that it knows that there are 2 records with the name "bologna" and 1 as > "bolognano." > > Thanks > > Il giorno venerdì 14 febbraio 2014 13:42:14 UTC+1, Binh Ly ha scritto: > > > I'm still not 100% sure I understand. Is this something that might work? > > > > { > > "query": { > > "multi\_match": { > > "query": "bologna", > > "fields": [ > > "locality", > > "region" > > ] > > } > > }, > > "script\_fields": { > > "bologna\_count": { > > "script": "\_index['locality']['bologna'].tf() + \_index['region'][' > > bologna'].tf()" > > } > > } > > } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/e01434e0-be58-45a1-8373-9636219ca569%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/e01434e0-be58-45a1-8373-9636219ca569%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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, 1:50am UTC](https://discuss.elastic.co/t/search-for-a-value-across-multiple-fields/15785/7 "2017-07-06T01:50:08Z")