Can I automate this task in elastic without leveraging external programming?

Elastic Stack Elasticsearch
1

One elastic index1 have documents like:

{username: u1, analysisId: 1}
{username: u1, analysisId: 2}
{username: u2, analysisId: 3}
{username: u3, analysisId: 4}
{username: u3, analysisId: 5}

In this index, analysis Id is like primary key and unique, while documents with multiple analysisId corresponds to one username, e.g., analysisId with 1 and 2 belongs to username, u1.

Another elastic index2 have documents like:

{username: u1}
{username: u2}

In this index, username is unique, like primary key.

Is it possible to filter all documents under index 1 using the primary key username in index 2 ONLY using elastic without external programming?

The steps would be like:
Step one:

GET /index2/_search
{
  "size": 1000,  // Adjust based on the expected number of usernames
  "_source": ["username"]
}

Step two: Then have the returned result from the script above and being used as the parameter for "all_usernames" in the second script:

PUT /index2/_doc/usernames_list
{
  "all_usernames": "the _source from the first script"
}

Then finally, Query index1 using the terms lookup with the id of the document created:

GET /index1/_search
{
  "query": {
    "terms": {
      "username": {
        "index": "index2",
        "id": "usernames_list",
        "path": "all_usernames"
      }
    }
  }
}

I checked with ChatGPT with an response that "Automating the those steps you mentioned directly in Elasticsearch isn't possible without external scripting or tooling, as Elasticsearch does not support chaining queries internally." I have experienced lots of BS from ChatGPT regarding Elastic, so now seeking source of truth from real person. Thanks!

2

In this instance ChatGPT is actally correct. Elasticsearch does not support joins or chaining of queries. A common way to get around this in Elasticsearch is to denormalise data, e.g. storing the data held about users in index2 in each of the documents in index 1.

3

Thanks for responding. Can you be more specific in " A common way to get around this in Elasticsearch is to denormalise data"? The username in index2 is already stored in each documents in index1.

4

Why do you then need to query index2?

5

To query index2 is to filter out those documents in index1 that are not listed in index2.