# Get records from index based on result from another search **URL:** \ **Category:** Elasticsearch\ **Created:** [October 30, 2023, 10:16pm UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074 "2023-10-30T22:16:01Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Mhag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhag/32/118090_2.png) [@Mhag](https://discuss.elastic.co/u/Mhag)\ **Post date:** [October 30, 2023, 10:16pm UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/1 "2023-10-30T22:16:01Z")
Hi, I have an index where we collect the requests to our api somthing like this : ```auto 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. ---
**Author:** ![Mhag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhag/32/118090_2.png) [@Mhag](https://discuss.elastic.co/u/Mhag)\ **Post date:** [October 31, 2023, 10:43am UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/2 "2023-10-31T10:43:42Z")
This is strange that Elasticsearch doesn't support subqueries ?! ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 31, 2023, 1:45pm UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/3 "2023-10-31T13:45:40Z")
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](https://www.elastic.co/blog/elasticsearch-query-language-esql) 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. ---
**Author:** ![Mhag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhag/32/118090_2.png) [@Mhag](https://discuss.elastic.co/u/Mhag)\ **Post date:** [October 31, 2023, 1:57pm UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/4 "2023-10-31T13:57:17Z")
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. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 31, 2023, 2:12pm UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/5 "2023-10-31T14:12:00Z")
I don't think you can do that but I might not be expert enough. 🙂 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: > **[Enrich your Elasticsearch documents within Elasticsearch](https://www.elastic.co/blog/enrich-your-elasticsearch-documents-within-elasticsearch)** > > With Elasticsearch, we know that joins should be done "at index time" instead of query time. This blog post starts a series of three posts as there are many approaches we can take within the Elastic ecosystem. ---
**Author:** ![Mhag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhag/32/118090_2.png) [@Mhag](https://discuss.elastic.co/u/Mhag)\ **Post date:** [November 1, 2023, 10:41am UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/6 "2023-11-01T10:41:01Z")
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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 29, 2023, 10:41am UTC](https://discuss.elastic.co/t/get-records-from-index-based-on-result-from-another-search/346074/7 "2023-11-29T10:41:20Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.