Search between two indexes

[rohit_pandey]

I have two indexes:

Index1

| id | name |
| 1 | A |
| 2 | B |
| 3 | Z |

Index2

| id | stmt_id | description |
| 1 | 11 | AAA
| 2 | 11 | BBB
| 3 | 11 | BBC
| 4 | 22 | DDD
| 5 | 22 | EEE
| 6 | 33 | FFF

All records are synced to elasticsearch for both indexes.

I want to match Index1 --> name with Index2 --> description.
My Input: [1, 2, 3] ( id of Index2 records.)
Now I want to match all Index1 records name with some Index2 records description.
And need output something like this:

{
    1(id of Index1 record) : [ 1 ] (id of index2 record). -- A matching with AAA,
    2 : [2, 3] -- B matching with BBB, BBC,
    3 : [ ] -- Z is not matching with any Index2 records description.
}

I want a query for doing this or any idea will be very helpful.
Thanks.

[warkolm (Mark Walkom)]

Welcome to our community!

The Elasticsearch approach to this would be to merge both sets of data into the same document and then index them. There is no way to do a join query like this with Elasticsearch.

[rohit_pandey]

Hi,

Can you give me more idea about merging both sets of data into the same document?

[warkolm (Mark Walkom)]

It'd basically be something like;

| id | name | stmt_id | description |

But as a json document, merged before it's sent to Elasticsearch.

[rohit_pandey]

I have only 200 documents for Index 1 and unknown documents for Index 2.

[rohit_pandey]

Done with _msearch and script_fields.