Get records from index based on result from another search

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.

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

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.

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.

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:

1 Like

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.