# Function Query with an aggregation function of nested field

**URL:** <https://discuss.elastic.co/t/function-query-with-an-aggregation-function-of-nested-field/19468>\
**Category:** Elasticsearch\
**Created:** [August 26, 2014, 5:14pm UTC](https://discuss.elastic.co/t/function-query-with-an-aggregation-function-of-nested-field/19468 "2014-08-26T17:14:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Srinivasan\_Ramaswamy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/srinivasan_ramaswamy/32/74106_2.png) [@Srinivasan\_Ramaswamy](https://discuss.elastic.co/u/Srinivasan_Ramaswamy)\
**Post date:** [August 26, 2014, 5:14pm UTC](https://discuss.elastic.co/t/function-query-with-an-aggregation-function-of-nested-field/19468/1 "2014-08-26T17:14:37Z")

</div>

I have documents with the above mentioned schema.

authorId : 10  
authorName: Joshua Bloch  
books: {  
{  
bookId: 101  
bookName: Effective Java  
description : effective java book with useful recommendations  
Category: 1  
sales: {  
{  
keyword: effective java  
count: 200  
},  
{  
keyword: java tips  
count: 100  
},  
{  
keyword: java joshua bloch  
count: 50  
}  
}  
createDate: 08-25-2014  
},  
{  
bookId: 102,  
bookName: Java Puzzlers  
description : Java Puzzlers: Traps, Pitfalls, and Corner Cases  
Category: 2  
sales: {  
{  
keyword: java puzzlers  
count: 100  
},  
{  
keyword: joshua bloch puzzler  
count: 50  
}  
}  
}  
}

The sales information is stored with each book along with the search query  
that lead to that sales. If the user applied a category filter, I would  
like to count only books that belong to that category.

I would like to sort the list of authors returned based on a function of  
sales data and text match. For eg if the search query is "java" I would  
like to return the above mentioned doc and all other author documents which  
has the term "java" in them. I came up with the following query:

{  
"query": {  
"function\_score": {  
"boost\_mode": "replace",  
"query": {  
"match": { "bookName":"java"}  
},  
"script\_score": {  
"params": {  
"param1": 2  
},  
"script": "doc['books.sales.count'].isEmpty() ? \_score :  
\_score \* doc['books.sales.count'].value \* param1 "  
}  
}  
}  
}

I have few questions with the query i have above

1. The results dont look sorted by sales. I have authors who dont have any  
books with sales in them at the top
2. How do i use the sum of all sales for an author (across all books within  
the author document) in the script ? Is there a sum function for the nested  
fields inside a document when using script\_score ? Note that sales is a  
nested field inside another nested field products.
3. As a next step I would also like to use a filter for keyword within the  
script\_score to only include sales whose keyword value matches with the  
search query term

Any help would be much appreciated.

Thanks  
Srini

--  
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/f858caee-bb43-45e1-ada3-212a78378aa0%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/f858caee-bb43-45e1-ada3-212a78378aa0%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Srinivasan\_Ramaswamy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/srinivasan_ramaswamy/32/74106_2.png) [@Srinivasan\_Ramaswamy](https://discuss.elastic.co/u/Srinivasan_Ramaswamy)\
**Post date:** [August 27, 2014, 5:38am UTC](https://discuss.elastic.co/t/function-query-with-an-aggregation-function-of-nested-field/19468/2 "2014-08-27T05:38:29Z")

</div>

Any thoughts anyone ? I am primarily looking for an answer to my 2nd  
question.

On Tuesday, August 26, 2014 10:14:37 AM UTC-7, Srinivasan Ramaswamy wrote:

> I have documents with the above mentioned schema.
> 
> authorId : 10  
> authorName: Joshua Bloch  
> books: {  
> {  
> bookId: 101  
> bookName: Effective Java  
> description : effective java book with useful recommendations  
> Category: 1  
> sales: {  
> {  
> keyword: effective java  
> count: 200  
> },  
> {  
> keyword: java tips  
> count: 100  
> },  
> {  
> keyword: java joshua bloch  
> count: 50  
> }  
> }  
> createDate: 08-25-2014  
> },  
> {  
> bookId: 102,  
> bookName: Java Puzzlers  
> description : Java Puzzlers: Traps, Pitfalls, and Corner Cases  
> Category: 2  
> sales: {  
> {  
> keyword: java puzzlers  
> count: 100  
> },  
> {  
> keyword: joshua bloch puzzler  
> count: 50  
> }  
> }  
> }  
> }
> 
> The sales information is stored with each book along with the search query  
> that lead to that sales. If the user applied a category filter, I would  
> like to count only books that belong to that category.
> 
> I would like to sort the list of authors returned based on a function of  
> sales data and text match. For eg if the search query is "java" I would  
> like to return the above mentioned doc and all other author documents which  
> has the term "java" in them. I came up with the following query:
> 
> {  
> "query": {  
> "function\_score": {  
> "boost\_mode": "replace",  
> "query": {  
> "match": { "bookName":"java"}  
> },  
> "script\_score": {  
> "params": {  
> "param1": 2  
> },  
> "script": "doc['books.sales.count'].isEmpty() ? \_score :  
> \_score \* doc['books.sales.count'].value \* param1 "  
> }  
> }  
> }  
> }
> 
> I have few questions with the query i have above
> 
> 1. The results dont look sorted by sales. I have authors who dont have any  
> books with sales in them at the top
> 2. How do i use the sum of all sales for an author (across all books  
> within the author document) in the script ? Is there a sum function for the  
> nested fields inside a document when using script\_score ? Note that sales  
> is a nested field inside another nested field products.
> 3. As a next step I would also like to use a filter for keyword within the  
> script\_score to only include sales whose keyword value matches with the  
> search query term
> 
> Any help would be much appreciated.
> 
> Thanks  
> Srini

--  
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/c86f6564-927d-4b9b-b9c6-4d8cdb4e72c1%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c86f6564-927d-4b9b-b9c6-4d8cdb4e72c1%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**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:06am UTC](https://discuss.elastic.co/t/function-query-with-an-aggregation-function-of-nested-field/19468/3 "2017-07-06T01:06:08Z")

</div>