# How to Correlate Data? **URL:** \ **Category:** Kibana\ **Created:** [April 18, 2018, 2:02pm UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547 "2018-04-18T14:02:38Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Christoph\_Kiefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christoph_kiefer/32/25287_2.png) [@Christoph\_Kiefer](https://discuss.elastic.co/u/Christoph_Kiefer)\ **Post date:** [April 18, 2018, 2:02pm UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/1 "2018-04-18T14:02:40Z")
Dear All Are there ways to mimic somehow the following SQL statement in Kibana / ES? // SELECT \* FROM network\_table // WHERE connection\_id IN ( // SELECT connection\_id FROM connection\_table // WHERE user\_id = "kiefer" // ) I'm trying to get a visualization of documents that could be joined to other documents by a specified field (connection\_id). Any hints are highly appreciated. Best regards Christoph ---
**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\ **Post date:** [April 18, 2018, 3:02pm UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/2 "2018-04-18T15:02:02Z")
That is not possible in Kibana and it's also not really possible in Elasticsearch. The general approach for a search engine like Elasticsearch would be to store your data in a denormalized form, instead of normalized forms, that you use in relational database systems, so have the `network_table` instead of a `connection_id` have the nested `connection` object in there. Cheers, Tim ---
**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\ **Post date:** [April 18, 2018, 3:02pm UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/3 "2018-04-18T15:02:42Z")
You can also find some additional information in the [Elasticsearch docs](https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html#joining-queries) about joining. ---
**Author:** ![Christoph\_Kiefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christoph_kiefer/32/25287_2.png) [@Christoph\_Kiefer](https://discuss.elastic.co/u/Christoph_Kiefer)\ **Post date:** [April 25, 2018, 1:25pm UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/4 "2018-04-25T13:25:50Z")
Dear Tim Thanks a lot for your reply. I appreciate it a lot. Also, I am big fan of your blog posts. I am trying to interpret your answer. First, you say that this is not possible in Kibana / ES unless one implements a denormalization step to bring the connection\_id field from the "connection\_table document" to the "network\_table document". Is that right? Would that happen somewhere in Logstash?What do you suggest? In your second answer, you post a link to ES docs about joining. Does that mean that it's nevertheless possible to do the joining in ES (Kibana?). Can we do it without changing our current mapping? Do you know of an any other example to demonstrate that? More generally, what is Elastic's answer when a customer wants to correlate the data from various sources (as it happens naturally in the RDBMS world)? Best regards Christoph ---
**Author:** ![timroes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timroes/32/19712_2.png) [@timroes](https://discuss.elastic.co/u/timroes)\ **Post date:** [April 27, 2018, 10:52am UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/5 "2018-04-27T10:52:40Z")
Hi, sorry yeah that answers where a bit confusing. Lemme clarify on that. Elasticsearch has some build in mechanisms for SOME kind of joins, that are described in the above linked documentation. Nevertheless the general advice is: denormalize your data. Also Kibana itself does not have support for any of those join possibilities ES offers, so you won't be able to visualize on it. We have nested aggregation support on our roadmap, but the nested fields imho doesn't solve the issue you describe above. So in the case of visualizing that documents, you should write the user\_id into the actual network\_table inside a `connection` field, so you could query on `connection.user_id` to check for that, instead of writing it in two indices. Hope that could clarify that a bit. > More generally, what is Elastic's answer when a customer wants to correlate the data from various sources (as it happens naturally in the RDBMS world)? Denormalizing it 🙂 Cheers, Tim ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 25, 2018, 10:52am UTC](https://discuss.elastic.co/t/how-to-correlate-data/128547/6 "2018-05-25T10:52:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.