Reference table

[Jim_Palazzolo]

Using ELK stack 6.x

I have two tables. One table is event tags the other is tags.
In the event tags table is the tag id. This tag id is used to look up in the tags table the text value associated with the tag. So, for example: event tag has an id of 3. In order to understand what 3 is the application looks for the value 3 in the tags table.

I can import these tables into the same index just fine. What I'm trying to do is have a search that basically says: look up the id value from the tags table data and replace the tag id value with the context from the tags table.

To expand on the tables here's the following describes:

event tagstable

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| event_id | int(11)    | NO   | MUL | NULL    |                |
| tag_id   | int(11)    | NO   | MUL | NULL    |                |
| local    | tinyint(1) | NO   |     | 0       |                |
+----------+------------+------+-----+---------+----------------+

tags table that is used for reference by the application.

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255) | NO   | MUL | NULL    |                |
| colour          | varchar(7)   | NO   |     | NULL    |                |
| exportable      | tinyint(1)   | NO   |     | NULL    |                |
| org_id          | int(11)      | NO   | MUL | 0       |                |
| user_id         | int(11)      | NO   | MUL | 0       |                |
| hide_tag        | tinyint(1)   | NO   |     | 0       |                |
| numerical_value | int(11)      | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

[nickpeihl]

With Elasticsearch, we recommend denormalizing your data. https://www.elastic.co/guide/en/elasticsearch/guide/master/relations.html

This means all items in your event tags table would also include the data in your tags table.

e.g.

PUT events/_doc/1
{
    "event_id" : 1,
    "local": 2,
    "name" : "some-name",
    "colour" : "#00000",
    "exportable": 0,
    ...
}

There is still a concept of relational joins in Elasticsearch. https://www.elastic.co/guide/en/elasticsearch/reference/7.2/parent-join.html But these types of queries are not supported by Kibana. https://github.com/elastic/kibana/issues/3730