Hi,
Is there any way to do join more than 2 indices?
Cheers,
Michael
Hi,
You can use the Elasticsearch’s syntax for writing index patterns. Also, the indices field that you join to is an array.
GET /siren/indexa,indexb/_search
{
"query": {
"join": {
"indices": [
"index1",
"index2",
"index_pattern"
],
"on": [
"fk",
"id"
],
"request": {
"query": {
"match_all": {}
}
}
}
}
}
The join query being part of the Elasticsearch’s query DSL, you can include as you wish it in other Elasticsearch queries such as bool. You can also nest joins within the request field.
@Stephane_Campinas
Can you explain more? How two indexes related to each other for joining?
From where can i execute this query? If this query is execute via Postman?
Thank you!
In the query above, fk is a field of indexa and indexb, while id is a field of index1, index2, and index_pattern. That join query will return you all the documents from indexa and indexb where you’ve got fk == id matching.
You can execute that query as you would any other Elasticsearch query, but pay attention that the path is prefixed with /siren.
1 Like
@Stephane_Campinas Thanks.
As i understand -
There is Indexa and Indexb needs to join with index1, index2 and index_pattern.
in response, i will get data of indexa and indexb.
is it correct?
Also, i want to make sure one more things, If i need to join index1, and index2 and need to data of both index in response. What is a better way to achieve this? Those data in millions , scroll API is supported for same? (or better way to achieve this?)
Also, Aggregation is supported? I need the sum of field name total from 2 indexes (index1 and index2). Is it achievable?
Once again thank you for helping me.
yes.
With a single join query it is not yet possible to get data from index2, just from index1, but the 10.4 release will support this use case. At the moment, you need to do the opposite join to get the data from index2.
Scroll API is supported.
The join query is like any other Elasticsearch query, e.g., term or range. So you create the subset you are interested in and then compute the buckets.
However, with the previous answer you cannot use data from index2. Can you elaborate on that particular use case ?
Hi Stephane,
I’m playing around with your plugin and wanted to understand if it would help with my usecase. With you’re example I want to join indexa with index1 with a filter condition and then want to join index 1 with index 2 with a different set of keys (so I cant reuse the on condition) Is this possible in one query ?
Example
Indexes = Account, Location, Client
Query to find account with client name "Bond: by joining across 3 indexes. The join keys are not the same like in your example, so would this be possible with one query.
Hope its clear.
Account{
accid: A1
location:{
locationid: S1
}
}
Location{
locationid:S1
client:{
clientid:C1
}
}
Client {
clientid:C1
clientName: “Bond”
}
Thanks for your help
HI @sanjaysunny,
I think the following query would answer your use case:
GET /siren/account/_search
{
"query": {
"join": {
"indices": [
"location"
],
"on": [
"location.locationid",
"locationid"
],
"request": {
"query": {
"join": {
"indices": [
"client"
],
"on": [
"client.clientid",
"clientid"
],
"request": {
"query": {
"term": {
"clientName": "Bond"
}
}
}
}
}
}
}
}
}
Cheers,
That is absolutely what I wanted. Thanks for the quick reply. Cheers
Hi,
If i need to join with two different indices with different foreign keys connecting them to the current index for a usecase say:
UseCase: get all EMPLOYEES who belong to a certain TEAM and a certain DEPARTMENT
NOTE:
- Joining across 3 different indices and join keys are not the same.
- EMPLOYEE has foreign key to TEAM (EmployeeFkToTeam) and DEPARTMENT (EmployeeFkToDepartment)
Will the below join query work?
GET /siren/employee/_search
{
“query”: {
"bool": {
"must": [
{
"term": {
"Some condition on employee": "some value"
}
},
{
"join": {
"indices": [
"team"
],
"on": [
"EmployeeFkToTeam",
"teamId"
],
"request": {
"query": {
"bool": {
"must": [
{
"term": {
"Some confition on team": "some value"
}
},
{
"join": {
"indices": [
"department"
],
"on": [
"EmployeeFkToDepartment",
"departmentId"
],
"request": {
"query": {
"term": {
"Some condition on department": "some value"
}
}
}
}
]
}
}
}
}
}
]
}
}
}
I found the correct way to phrase this query and this one works! Posting it here as it might be helpful for anyone looking for a similar query.
UseCase: get all EMPLOYEES who belong to a certain TEAM and a certain DEPARTMENT
NOTE:
- Joining across 3 different indices and join keys are not the same.
- EMPLOYEE has foreign key to TEAM (EmployeeFkToTeam) and a different foreign key to DEPARTMENT (EmployeeFkToDepartment)
GET /siren/employee/_search
{
"query": {
"bool": {
"must": [
{
"term": {
"Some condition on employee": "some value"
},
{
"join": {
"indices": [
"department"
],
"on": [
"EmployeeFkToDepartment",
"departmentId"
],
"request": {
"query": {
"term": {
"Some condition on department": "some value"
}
}
}
}
},
{
"join": {
"indices": [
"team"
],
"on": [
"EmployeeFkToTeam",
"teamId"
],
"request": {
"query": {
"term": {
"Some confition on team": "some value"
}
}
}
}
}
]
}
}
}
@Sangeetha_R Thanks! Got it - you joined table #1 on table #2 and tables #2 on #3 right?
Is there an option to join #1 with #2 and #3?