Get records from index based on result from another search

Elastic Stack Elasticsearch
1

Hi,

I have an index where we collect the requests to our api somthing like this :

myindex:
   url: /some/path
   service: someservice
   uuid: xxx-yyy-zzz-uuu

And I have a requirement to get or correlate all urls that match the uuid from a query in the same index with url=/specific/path.

I tried to get this with SQL but it does not support subqueries yet, in sql it would be somthing like this :

select T1.url from myindex AS T1 where T1.uuid in (select T2.uuid from myindex T2 where T2.url="/specific/path" ).

I hope it's clear, Thanks for your help.

Regards.

2

This is strange that Elasticsearch doesn't support subqueries ?!

3

It's because Elasticsearch is not a relational database. So what you can find obvious in relational database might be a bit trickier in a Document oriented search engine.

I think that the coming ES|QL engine might be able to solve such use cases but I did not test it yet.

I believe that for now you would need to run 2 queries separately.

4

Thanks for your reply.

The problem is; I need the result to be used as source of a Kibana visualization, can Kibana run two queries and agrregate the result based on a field ?

Reagards.

5

I don't think you can do that but I might not be expert enough. :slight_smile:
May be ask for this specific question in Kibana

What normally people do is to perform some join at index time...

I wrote a blog post on that:

6

Thanks David,

It gives me some ideas how we can do that, however one problem here is I have huge indices (~ 200 Go daily ) reindexing this can be very slow on this infra.

So I think another easy way is to add the filed of the join on the docs, this way we can filter directly in the index.

I post another question on the Kibana forum, if somone have an idea how to do it via Kibana.

Thanks again the article was very helpful.