How to access children documents for a scripted query

Elastic Stack Elasticsearch
1

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!)

2

For this kind of behavioural stuff I normally advise re-orientating data around entity-centric indexes. Why and how here : https://www.youtube.com/watch?v=yBf7oeJKH2Y

3

Thanks! looks interesting.

4

Video is interesting - but it seems we're already doing what he's speaking about (eg, the users/user_days are entities that we query..) - but I'll review more to see if I can get more ideas.

Incidentally - if these were nested documents instead of children documents, it might be possible to do ala http://asquera.de/blog/2014-06-02/elasticsearch-script-filters/

5

What I'm talking about in the video is one JSON doc per user.

Right now you have one doc per user per day together with the sort of query that ticks the boxes for what I call the "Coke and Mentos" recipe: https://speakerdeck.com/elastic/building-entity-centric-indexes?slide=5

The http://bit.ly/entcent scripts I provide as an example provide the basis for building single-JSON-doc entity-centric indexes from your event store.

6

(didn't notice it's you in the video :slight_smile: )

ok - that's what I said in reference to if they were nested instead of children. But then we lose all the cool things we can do because they are children. If there is no other way - we might sync from the children to nested to enable this kind of query. As you pointed out from the bank example, it's also possible to have a "coke and mentos" recipe with a single document.