Using Application join

[Haneul_Kim]

by reading https://www.elastic.co/guide/en/elasticsearch/guide/current/application-joins.html#application-joins Now I understand I can get list of values then use them as a filter.

Since version 5.0 "filtered" query has been deprecated and now I've replaced with "bool" query. https://www.elastic.co/guide/en/elasticsearch/reference/7.0/query-dsl-filtered-query.html

I cannot find a way to populate result from the first query.

My first query:

GET /access_log/_search?size=5
{
  "query": {
    "bool": {
      "must":[
        {"term":{"status":200}}
      ],
      "should": [
        {"match": {"request_1": "supporter-manager"}},
        {"match": {"request_2": "deploy"}}
      ],
      "minimum_should_match": 2
    }
  }
}

So here I want all "user_id" that match above query and pass fill terms query in 2nd query

GET /access_log/_search?
{
  "query":{
    "bool":{
      "filter":{
        "terms":{
          "user_id":[]
        }
      },
      "should":[
        {"match": {"request_1": "rent"}},
        {"match": {"request_2": "start"}}
      ]
    }
  }
} 

I want to populate result of 1st query into "terms":{"user_id":[**here**]} but I'm confused, How can I achieve this?

From my understanding field name must be specified so only possibilities would be passing in empty list just like my query above or pass nothing (doesn't work either).

I've also tried using "must" clause and multi_match like this:
GET /access_log/_search?

{
  "query":{
    "bool":{
      "must":{
        "multi_match":{
          "operator":"or",
          "fields":["user_id"]
        }
      }
    }
  }
}

but it keeps on giving me "No text specified for multi_match query" which I think is because I don't have "query" clause but then I am trying to fill that with list of user_id from query 1, again pass empty list ?

So finally I get document that contain "request_1":"rent", "request_2":"start" and their "user_id" must be from query 1.

[Christian_Dahlqvist]

It is called application side join due to the fact that you need to create the terms clause in the second query based on the results from the first in your application code.

[Haneul_Kim]

oh so the first query needs to be inside ["here"] ?

I am trying

GET /access_log/_search?
{
  "query":{
    "bool":{
      "should":[{
        "terms":{
          "serial_number":[
            {"query":{
              "bool":{
                "should":[
                  {"match":{"request_1":"supporter-manager"}},
                  {"match":{"request_1":"deploy"}}
                ],
                "minimum_should_match":2
              }
            }}
          ]
        }
      }],
      "must": [
        {"match": {"request_1": "rent"}},
        {"match": {"request_2": "start"}}
      ]
    }
  }
} 

It keeps on giving me [terms] query does not support [minimum_should_match] I've followed https://stackoverflow.com/questions/43633472/how-to-simulate-multiple-fields-in-a-terms-query and other related posts and they seem to use the same method and get it to work.

[Christian_Dahlqvist]

You can not do it in a single query. You need to run your first query, retrieve the list of terms returned and use this to construct the second query using the programming language of your choice. Once this is complete you can send the second query to Elasticsearch.

[Haneul_Kim]

I cannot accomplish this in elasticsearch, without using other programming language? Also how do I even get list of hits values, it will it to me in list of dictionaries even if I only retrieve field I want using _source cluase.

[Christian_Dahlqvist]

You need to do this in a programming language in your application.