How to filter data using two indexes

[lakmina_chathurya]

hi team,

I want to select all the users from user index and filter software engineers from user_job index and result should display in bucket aggregation

[dadoonet (David Pilato)]

Welcome.

What did you do so far?

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

[lakmina_chathurya]

hi @dadoonet
this is my query

GET  metric/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
               
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "group": {
      "terms": {
        "field": "user.keyword",
        "size": 10000
      },
      "aggs": {
        "Score": {
          "terms": {
            "script": {
              "lang": "painless",
              "source": "\r\n              long fixed= 0 ;\r\n"
            }
          }
        }
      }
    }
  }
}

this is my result

"aggregations" : {
"group" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "david warner",
      "doc_count" : 203,
      "Score" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "1.0",
            "doc_count" : 84
          },
          {
            "key" : "2.0",
            "doc_count" : 49
          }]}}]}}

i want to check these user roles are engineer. And user_roles field in another index called user_role. i need a buckets aggregation results after checking users are engineer.(it is something like select users from user and select user from user_role where role=engineer) . need to deal with two indexes

thank you

[dadoonet (David Pilato)]

@lakmina_chathurya

(it is something like select users from user and select user from user_role where role=engineer) . need to deal with two indexes

You can't do that in elasticsearch (doing joins at search time).
Unless you use the parent/child feature - but I'd use it only and only if I don't have any other choice.

I prefer doing joins at index time instead. Which means only index one type of document.

[lakmina_chathurya]

thank you @dadoonet