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?
Joining queriesedit Instead, Elasticsearch offers two forms of join which are designed to scale horizontally. Documents may contain fields of type nested . These fields are used to index arrays of objects, where each object can be queried (with the nested query) as an independent document.
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 email
s (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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With