Has_child query does not support post_filter?


(george_monroe) #1

Guys, I'm running the following query to test out one of the use cases:

curl -X GET '0:9200/segmentation/animal/_search?pretty' -d

'{ "query" : {
"has_child" : { "type" : "visit",
"query" : {
"bool" : {
"must" : [
{"term" : { "_parent" : "119000148-5661691" }},
{ "range" : {
"visit_date" : {
"from" : "2003-01-01T00:00:00.000Z",
"to":"2011-11-18T00:00:00.000Z",
"include_lower" : false,
"include_upper" : true}}}]}},
"post_filter" : { "script": { "script": "true == true" }}}}}}}'
<==== Was hoping to use a custom native script to sum across the Visit docs
to compare to sum threshold.

I have Animal parent with child Visit. I am trying to solve this very
important use case for us: Give me all Animals that have had visits between
a certain date range, and those visits must sum to a certain $ amount.

What I would like to do is filter out children records first by the date
range, and then filter by the sum (which is the some across that date-range
collection of docs).

In other words, I would like to return only those Animals in the result
set, which have visits totaling a certain sum, and those visits being
within a certain date range.

Is there a better way to do this?

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/82d03aa4-b003-4e8c-a7b6-8d7129fdfbe0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Binh Ly-2) #2

If I understand you correctly, you what to return all parents where the the
sum of a field (child.field_to_sum) of each parent's corresponding children
exceeds some number (min_sum_value) and at the same time only for those
children whose date falls in some range. You should be able to do something
like this:

{
"min_score": <min_sum_value>,
"query": {
"has_child": {
"type": "child",
"score_type": "sum",
"query": {
"function_score": {
"filter": { + Whatever else }
},
"script_score": {
"script": "doc['child.field_to_sum'].value"
}
}
}
}
}
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/9fe5307f-2b70-4be8-ac78-cf2c752c85e3%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(george_monroe) #3

Binh! Thanks so much!! It worked. But how do I limit it from the top
based on max score??

I have an additional complication in that the parent has multiple children
types. I figured out how to assign score of 0 to the children that should
not contribute to the final score, but now I would like to say: Give me all
the parents whose set of children fall within a certain date range, and
whose corresponding sum of child.column_to_sum is at least MIN_SCORE but
not more than MAX_SCORE.

How would I do that?

Here is the query that I've written based on your very helpful hint!

curl -X GET '0:9200/segmentation-cd/animal/_search?pretty' -d
'{ "min_score" : 250,
"query" : {
"bool" : {
"must" : [
{ "function_score" : {
"query" : {
"term" : { "animal.sex" : "neutered" }},
"script_score" : {"script" : "0"}}},
{ "has_child" : {
"type" : "customer",
"score_type" : "sum",
"query" : {
"function_score" : {
"filter" : {
"query" : {
"term" : { "customer.first_name" : "Michael"
}}},
"script_score" : {"script" : "0"}}}}},
{ "has_child" : {
"type" : "visit",
"score_type" : "sum",
"query" : {
"function_score" : {
"filter" : {
"bool" : {
"must" : [
{ "range" : { "visit_date" : {
"from" : "2001-07-28T00:00:00.000Z", "to" : "2011-11-18T00:00:00.000Z",
"include_lower" : false, "include_upper" : true}}}]}},
"script_score" : {"script" : "_source.revenue"}}}}}]}}}'

On Wednesday, March 5, 2014 5:48:32 AM UTC-8, Binh Ly wrote:

If I understand you correctly, you what to return all parents where the
the sum of a field (child.field_to_sum) of each parent's corresponding
children exceeds some number (min_sum_value) and at the same time only for
those children whose date falls in some range. You should be able to do
something like this:

{
"min_score": <min_sum_value>,
"query": {
"has_child": {
"type": "child",
"score_type": "sum",
"query": {
"function_score": {
"filter": { + Whatever else }
},
"script_score": {
"script": "doc['child.field_to_sum'].value"
}
}
}
}
}
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/151e2db0-9c6d-40bd-b41b-2f2f8df998bd%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(system) #4