Workaround for 'JOIN' in Kibana/ES

Elastic Stack Elasticsearch
1

I use Elasticsearch to document URLs requested by users.

I know that since this is a NoSQL database, there are issues with performing joins. I've read up on nested queries as well as parent/child aggregations, but am unsure if they'll solve my issue:

I need to find URLs accessed by a certain user, which were then later accessed by any other user.

I know in SQL I could do a join, e.g.

get 
  URL as infra_url,
   timestamp as infra_timestamp
     from url_index
     where username = 'infra' 
Join 
  URL as user_url,
   timestamp as user_timestamp
     from url_index
     where username != 'infra' 
     
     where infra_url = user_url and infra_timestamp < user_timestamp

Is there a similar way to perform this is Kibana/ES? I'm currently using the v5.6.2 stack.

2

You need to do one of a few things:

  • Index the URL with infra/user nested/sub-objects/child docs
  • Perform multiple queries

If you're using Kibana, you'll really need to completely denormalize on index and just shove it all into the same doc.