Elasticsearch query Combine 2 queries or Elastic search Join Query

[vipin.meshram13098 (Vipin)]

This is my one elastic document example:

"_source": {
    "leg_b":{
            "team_name" : "aaaa",
            "channel_uuid" : "12121"
    },  
    "wrapup":[
      "0":{
            "name": "sss",
            "channel_uuid": "12121"
          },
      "1":{
            "name": "bbb",
            "channel_uuid" : "21212"
          },
      "2":{
            "name": "acbs",
            "channel_uuid" : "111211"
          }
     ]
}

I have to perform search on "leg_b.team_name" value and find out its "channel_uuid" value on the basis of leg_b's "channel_uuid",NEXT I will search in wrapup object for common "channel_uuid" values in the Same Query .

Example , lets consider above document, First I will searchon leg_b.team_name = "aaaa" after that I will take leg_b.channel_uuid = "12121"and search on wrapup object channel_uuid.

So it should return,

"_source": {
"leg_b":{
        "team_name" : "aaaa",
        "channel_uuid" : "12121"
},  
"wrapup":[
  "0":{
        "name": "sss",
        "channel_uuid": "12121"
      },
  "1":{
        "name": "bbb",
        "channel_uuid" : "21212"
      }
      ]
}

Please suggest some approach, Thank you.

[RabBit_BR (andre.coelho)]

Hi!

I would get the docs by the "team_name" field and my application would filter the channel_uuid in the wrapup.

[Tomo_M (Tomohiro Mitani)]

Is there any problem with filtering the nested wrapup documents by client side?

Or one possible way is to adda new "same_channnel_uuid_as_leg_b" field in wrapup objects and use nested query on that field. With this strategy, if a document has no appropriate wrapup object, the document itself drops from the search result. If you also need such documents, you have to use boolean queries not to drop them (something like should clause with minimum_should_match: 0 and inner_hits parameters.

[vipin.meshram13098 (Vipin)]

@Tomo_M ,
Minimum Should Match is another search technique that allows you to conduct a more controlled search on related or co-occurring topics by specifying the number of search terms or phrases in the query that should occur within the records returned.

but in my case I need all the wrapup.Channel_uuid records which is common in wrapup object. So, these all should return me in one query.

[Tomo_M (Tomohiro Mitani)]

My first question was why you need to get such return in one query. I hope you will answer this question.

My intention about minimum_should_match:0 was:

PUT test_filter_nested
{
  "mappings": {
    "properties": {
      "wrapup":{
        "type":"nested",
        "properties": {
          "same_channnel_uuid_as_leg_b":{
            "type": "boolean"
          }
        }
      }
    }
  }
}

POST test_filter_nested/_doc
{
  "leg_b":{
            "team_name" : "aaaa",
            "channel_uuid" : "12121"
    },  
    "wrapup":[
      {
            "name": "sss",
            "channel_uuid": "12121",
            "same_channnel_uuid_as_leg_b": true
          },
      {
            "name": "bbb",
            "channel_uuid" : "12121",
            "same_channnel_uuid_as_leg_b": true
          },
      {
            "name": "acbs",
            "channel_uuid" : "111211",
            "same_channnel_uuid_as_leg_b": false
          }
     ]
}

POST test_filter_nested/_doc
{
  "leg_b":{
            "team_name" : "aaaa",
            "channel_uuid" : "12121"
    },  
    "wrapup":[
      {
            "name": "acbs",
            "channel_uuid" : "111211",
            "same_channnel_uuid_as_leg_b": false
          }
     ]
}

GET test_filter_nested/_search

GET  test_filter_nested/_search?filter_path=hits.hits._source,hits.hits.inner_hits.wrapup.hits.hits._source
{
  "query":{
    "bool": {
      "must": [
        {"term": {
          "leg_b.team_name": {
            "value": "aaaa"
          }
        }}
      ], 
      "should":[
      {
        "nested": {
          "path": "wrapup",
          "query": {
            "term": {
              "wrapup.same_channnel_uuid_as_leg_b": {
                "value": true
              }
            }
          },
          "inner_hits": {}
        }
        
      }],
      "minimum_should_match": 0
    }
  },
  "_source":"leg_b"
}

Then you get:

{
  "hits" : {
    "hits" : [
      {
        "_source" : {
          "leg_b" : {
            "channel_uuid" : "12121",
            "team_name" : "aaaa"
          }
        },
        "inner_hits" : {
          "wrapup" : {
            "hits" : {
              "hits" : [
                {
                  "_source" : {
                    "name" : "sss",
                    "channel_uuid" : "12121",
                    "same_channnel_uuid_as_leg_b" : true
                  }
                },
                {
                  "_source" : {
                    "name" : "bbb",
                    "channel_uuid" : "12121",
                    "same_channnel_uuid_as_leg_b" : true
                  }
                }
              ]
            }
          }
        }
      },
      {
        "_source" : {
          "leg_b" : {
            "channel_uuid" : "12121",
            "team_name" : "aaaa"
          }
        }
      }
    ]
  }
}

This may be one of some approaches.