opensearch/elasticsearch query second index based on results from first

Question

is there a way to get results from a second index based on the results from the first?

For example, patients index and appointments index:

• patients index => id, name, phone, organization

• appointments index => id, patientId

  POST patients/_search { "query": { "bool": { "must": [ { "match": { "organization.S": "whatever" } } ] } } }

  POST appointments/_count { "query": { "bool": { "must": [ { "match": { "patient.S": "patientId" } } ] } } }

the first one would get me my patients, and the second one would count how many appointments a patient has.

How can I merge them to get from the first one the patients + their number of appointments in the same query?

Basically, a graphql kind of thing where the number of appointments would be resolved into the first query in parallel or something of the sort.

Thank you in advance!

Answer 1

Also, if you want to see all patients and with all organizations, you can use the following aggregation.

POST patients/_search
{
  "aggs": {
    "organizations": {
      "terms": {
        "field": "organization.S",
        "size": 1000
      },
      "aggs": {
        "patients": {
          "terms": {
            "field": "patient.S",
            "size": 1000
          }
        }
      }
    }
  }
}

Answer 2

You can use the term aggregation.

POST patients/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "organization.S": "whatever"
          }
        }
      ]
    }
  },
  "aggs": {
    "NAME": {
      "terms": {
        "field": "patient.S",
        "size": 1000
      }
    }
  }
}

Note: the field type should be keyword for the aggs. If you get any error, try with your_field_name.keyword.