Search between two indexes

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.

Welcome to our community! :smiley:

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.

Hi,

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

It'd basically be something like;

| id | name | stmt_id | description |

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

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

Done with _msearch and script_fields.