I have a index that has a "user" document and a child document called "user_day" which includes the actions the person did on a certain day. Let's say that the user_day document includes "num_actions" and "days_since_signup" fields.
I would like to find the users that have num_actions on one day equal to num_actions on the next day.
I tried a scripted query - but I don't know if or how to access the children documents in the script (I'm using inner_hits). I'm thinking that's not possible because the children are filled in after the parent match. But maybe I'm wrong.
I thought perhaps I could do it with a pipeline aggregation - but I'm also not sure if that makes sense or helps. If I'm querying the users - I'm not sure that there are multiple things to combine or compare. Again, I'm not sure and I'm very new to the pipeline aggregations.
My last ideas is that I ask for user_day that has a parent of user that has a child of user_day with a scripted query that references the top parent document. But, again, I don't know how to access the parent document, if at all possible, in the script.
Does anyone know if/how I could do this?
(please don't reply that I really should be using a relational database!)