Joining Two Indexes with common field values

Elastic Stack Elasticsearch
1

Hi,

I am trying to join two indexes with common field values. Can someone please help me.

Here is the example:

Index_1 => A
column_1 => value_1

Index_2 => B
column_2 => value_1

How can i join both indexes on the match of value_1?

Thanks in Advance

2

Elasticsearch does not support joins so what you are trying to do is not possible. You will therefore need to change how you index and structure your data. If you can provide some details about your data and the problem you are trying to solve the community might be able to help.

3

Hi,

I am taking data from 2 csv files and getting indexed into ES. My requirement is I need to create a bar graph when the specific field value matches with the another field value of other index.

In Sql, usually it possible through inner and outer join queries. I am expecting same in elasticsearch as well.

please share some suggestions.

Thanks

4

Elasticsearch is not a relational database and does not support joins.

I would recommend you denormalise and perform the join ahead of indexing the data, e.g. using an ingest pipeline with an enrich processor.

5

but it is possible to do than inner join.
if you do a request with only "value_1", you see only all row in your 2 index no ?
a dashboard with table and request with a "WHERE" "value_1" ?

6

I do not understand what you mean. Can you please elaborate?

7

ok.

My way of doing it is a little twisted but comes from my experience in managing data files.
imagine you have 3 tables.
Table_A and Table_B and Table_C
then in each of the ID tables.

I concatenate the table name with its id for each table.
In your table A you will have an additional column that looks like this:
"Table_A 54687561615 Table_B 7445123214"
"Table_A 54687561615 Table_B 7445123215"
"Table_A 54687561615 Table_B 7445123216"

Table B: (here it is you need bidirectional query)
"Table_B 7445123214 Table_A 54687561615
"Table_B 7445123215 Table_A 54687561615
"Table_B 7445123215 Table_C 1615
"Table_B 7445123216 Table_A 54687561615
"Table_B 8575123244 Table_C 1615

Table C:
"Table_C 1615 Table_B 7445123215"
"Table_C 1615 Table_B 8575123244"

this column is a character string which will have the name of the table SPACE its id SPACE name of the table 2 SPACE its id

if you search on an ID, as this field in Elasticsearch is (must) be a text, its search method is of type phrase. i.e. each space is not considered as an indexing character.
a search for "Table_C 1615" will return the rows from table B and C that match:
"Table_B 7445123215 Table_C 1615"
"Table_B 8575123244 Table_C 1615"
"Table_C 1615 Table_B 7445123215"
"Table_C 1615 Table_B 8575123244"

which corresponds to a somewhat crude innerjoin but you do have all the information of your 4 elements 2 in table B and 2 in table C.

8

Are you saying you put all of these "tables" into a single index in Elasticsearch?

9

Yes, on the match of value_1.

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.