Elasticsearch Query Help - Multiple Nested AND/OR -

i struggling elasticsearch filters. have company_office type looks this:

{   "company_office_id": 1,   "is_headquarters": true,   "company": {     "name": "some company inc"   },   "attribute_values": [       {         "attribute_id": 1,         "attribute_value": "attribute 1 value",       },       {         "attribute_id": 2,         "attribute_value": "abc",       },       {         "attribute_id": 3,         "attribute_value": "def",       },       {         "attribute_id": 3,         "attribute_value": "hij",       }   ] } 

let's assume attribute_value not_analyzed - can match on exactly.

now want filter on combination of multiple attribute_id , value fields. in sql:

select * companyoffice c join attributes --omitting on here, assume join valid  c.is_headquarters = true , (   (a.attribute_id=2 , a.attribute_value in ('abc')) or   (a.attribute_id=3 , a.attribute_value in ('def','hij'))  ) 

so need filter on specific fields + multiple combinations of id/value.

here query tried:

{   "query" : {     "filtered" : {       "filter" : {         "bool" : {           "must" : [               { "term": {"is_headquarters": true } },             {"bool": {                        "must":[                         {"term": {"attribute_values.attribute_id": 1}},                         {"bool": { "should": [{"term": {"attribute_values.attribute_value": "hij"}}]}}                       ]                      }}                     ]          }        }      }    }  } 

this query returning results company_office not have id/value pairing of 1/'hij'. thinking here because bool filter sitting inside of parent must section, items must true:

            {"bool": {                        "must":[                         {"term": {"attribute_values.attribute_id": 1}},                         {"bool": { "should": [{"term": {"attribute_values.attribute_value": "hij"}}]}}                       ]                      }}   

why query return results given data sample provided @ beginning of question? there different way write filter , accomplish trying do?

thanks help!

if want query deeper objects without flattening structure, need set

"type": "nested" 

on "attribute_values" property.

then refer how write nested queries in documentation, , should correctly retrieve whole document. use inner hits retrieve matched attribute_values.

by default, elasticsearch not nest properties when indexing. subfields get's squashed separate subfields without ability query them actual structure. not see effect, because original document returned.

apart that, queries bit off. in last "should" statement, have 1 term filter it's "must" part, have rewritten nested format.