Kibana: joining two documents in table visualization

Question

I have a data table visualization displaying fields from documents that have an email address and an id:

timestamp |         email       |  field_id
 Feb 5th       [email protected]       xyz123

These fields belong to the same elastic document. However, I have other documents with extra information pertaining to this unique id, and can display these as their own table:

timestamp |  field_id   |    key1   |   key2   |  key3
  Feb 6th     xyz123         val1       val2      val3

You can see the row in the first table and the one on the second table have the field_id in common. What I'd like to know is whether it is possible to display a merged row with Kibana and/or an elastic query:

 field_id   |     email      |    key1   |   key2   |  key3
   xyz123     [email protected]      val1       val2      val3

This would be somewhat equivalent to a join for a relation database in SQL. If this is not possible in Kibana, maybe there is a way to achieve this indirectly with a query using the json input with elastic and perform a kind of application-side join?

Answer 1

It looks like you try using your knowledge of relational databases with no-SQL databases such as Elasticsearch (ES). There are several options you have.

Option #1. Save all the information you have into the same document. If you get more data after an initial document was indexed, just update it with extra keys. If different documents have different schemas (aka set of keys), it's not a problem for ES. Also, when querying ES, you can specify which fields do you want to retrieve if you are concerned about size of requests/responses with ES.

Option #2. You can use different types for your different id/email documents, and id/keys documents, but keep storing them in the same index. Then, you can create a dashboard and put several visualizations: a) Data Table with ability to choose an id; b) Email visualization which shows all emails (as soon as you select an id in visualization a) by clicking on it, you Kibana will immediately show you an email of the document for the given id c) Keys visualization which shows all keys (again, as soon as you select an id or an email, this visualization will update to show only keys related to the selection)

Option #3. Same as above, but you can have different indices instead of a different types. As long as those indices have a common prefix (e.g. docs-email and docs-keys), you can use their prefix in kibana to retrieve data from different indices

Option #4. Application-level join as you described in your question. ES provides REST API over all the data it stores. You can always retrieve whatever you need from it and build client-side join (it makes one wonder why did you choose ES as a backend for storing data instead of a relational DB)