Merging two indexes by a column

[Juanfer (Juan Espinosa)]

Hello there,

I am a beginner in Elasticsearch and I have been reading about how to merge 2 indexes based on two different names columns with the same values. I understand the NoSQL performance of elastic but still having a hard time performing the queries. For instance I have the following:
Index_1: Laptops-id-area

{
        "_index": "laptops-demo",
        "_id": "1",
        "_score": 1,
        "_source": {
          "id": 1,
          "name": "marketing"
        }
      },
      {
        "_index": "laptops-demo",
        "_id": "2",
        "_score": 1,
        "_source": {
          "id": 2,
          "name": "Finance"
        }
      },
      {
        "_index": "laptops-demo",
        "_id": "3",
        "_score": 1,
        "_source": {
          "id": 3,
          "name": "Sales"
        }

Index_2: laptops

{
        "_index": "laptops",
        "_id": "1",
        "_score": 1,
        "_source": {
          "id": 1,
          "name": "lenovo",
          "description": "8GB 2022",
          "laptop_id": 3
        }
      },
      {
        "_index": "laptops",
        "_id": "2",
        "_score": 1,
        "_source": {
          "id": 2,
          "name": "Vaio",
          "description": "32GB 2019",
          "laptop_id": 2
        }
      },
      {
        "_index": "laptops",
        "_id": "3",
        "_score": 1,
        "_source": {
          "id": 3,
          "name": "Dell",
          "description": "Intel core i7 64GB 2021",
          "laptop_id": 1
        }

Since the fields index_1.id == index_2.laptop_id I would like to output a mapping of the index_2 fields with the name of index_1.name. Output:

"_id",  "_score", "name", "description", "laptop_id",  "name from index 1"

Could you please be so gentle in directing me in the right coding script? Thank you in advance

[JLeysens (Jean-Louis Leysens)]

Hello @Juanfer ! I'd recommend reading this docs Joining queries | Elasticsearch Guide [8.4] | Elastic

You are correct though, full SQL join queries are not supported in Elasticsearch. I'd say think about why you want to separate your data into 2 indices in the first place. Could they not be one?

[Juanfer (Juan Espinosa)]

Hello Jean-Louis, thank you for your replied. I already read the file. We are pulling data from different APIs to Elasticsearch and therefore the different indexes created. I aimed to avoid doing a before step where I merge all the datasets together and after allocating in ES as merging the data in Elasticsearch is the easiest step to make. I am afraid that is not possible and the solution would be to merge the data before allocating in Elastic, am I right?