elasticsearch mix "and filter" with "bool filter"

Question

i work on elasticsearch, I try to mix two working queries. the first with "and filter" and the second with "bool filter" but i fail.

My queries are generated dynamically from a user interface.

1. the "and filter" :

I need "and filter" to query data, for example a field have to be equal to "africa" or "asia" or empty. this is an example of working query :

curl -XGET 'http://localhost:9200/botanique/specimens/_search?pretty' -d '
{
    "fields" : ["D_TYPESTATUS", "O_HASMEDIA"],
    "aggs" : {
        "D_TYPESTATUS_MISSING" : {
            "missing" : {
                "field" : "D_TYPESTATUS"
            }
        },
        "D_TYPESTATUS" : {
            "terms" : {
                "field" : "D_TYPESTATUS",
                "size" : 10
            }
        }
    },
    "query" : {
        "filtered" : {
            "filter" : {
                "and" : [
                    { "or" : [{
                                "term" : {
                                    "O_HASMEDIA" : "true"
                                }
                            }
                        ]
                    }, {
                        "or" : [{
                                "term" : {
                                    "T_GENUS" : "flemingia"
                                }
                            }
                        ]
                    }, {
                        "or" : [{
                                "term" : {
                                    "L_CONTINENT" : "africa"
                                }
                            }, {
                                "term" : {
                                    "L_CONTINENT" : "asia"
                                }
                            }, {
                                "missing" : {
                                    "field" : "L_CONTINENT"
                                }
                            }
                        ]
                    }, {
                        "or" : [{
                                "term" : {
                                    "I_INSTITUTIONCODE" : "mnhn"
                                }
                            }
                        ]
                    }
                ]
            }
        }
     }
}'

this query work fine, this is the result :

 "hits" : {
    "total" : 1006,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "botanique",
      "_type" : "specimens",
      "_id" : "9459AB31EC354F1FAE270BDB6C22CDF7",
      "_score" : 1.0,
      "fields" : {
        "O_HASMEDIA" : [ true ],
        "D_TYPESTATUS" : "syntype"
      }
    }, 
    ....
  },
  "aggregations" : {
    "D_TYPESTATUS" : {
      "buckets" : [ {
        "key" : "syntype",
        "doc_count" : 6
      }, {
        "key" : "type",
        "doc_count" : 5
      }, {
        "key" : "isotype",
        "doc_count" : 2
      } ]
    },
    "D_TYPESTATUS_MISSING" : {
      "doc_count" : 993
    }
  }

}

2. the second query :

Now i need to restrict the result data with the field : "D_TYPESTATUS" who must be different from the value "type" and must be not null.

this query work to do this :

curl -XGET 'http://localhost:9200/botanique/specimens/_search?size=10&pretty' -d '    {
    "fields" : ["D_TYPESTATUS", "O_HASMEDIA"],
    "aggs" : {
        "D_TYPESTATUS_MISSING" : {
            "missing" : {"field" : "D_TYPESTATUS"}
        },
        "D_TYPESTATUS" : {
            "terms" : {"field" : "D_TYPESTATUS","size" : 20}
        }
    },
    "query" : { 
        "filtered" : {
            "query" : {
                "query_string" : {  "query" : "liliaceae"  }
            },
            "filter" : {
                "bool" : {
                    "must_not" : [{
                            "term" : {
                                "D_TYPESTATUS" : "type"
                            }
                        }
                    ],
                    "must":{
                            "exists" : {
                                "field" : "D_TYPESTATUS"
                        }
                    }
                }
            }
        }
    }
}'

and the result :

 {[ {
    "_index" : "botanique_tmp2",
    "_type" : "specimens",
    "_id" : "0C388B4A3186410CBA46826BA296ECBC",
    "_score" : 0.9641713,
    "fields" : {
      "D_TYPESTATUS" : [ "isotype" ],
      "O_HASMEDIA" : [ true ]
    }
  } , ... ]},
"aggregations" : {
  "D_TYPESTATUS" : {
    "buckets" : [ {
      "key" : "isotype",
      "doc_count" : 40
    }, {
      "key" : "syntype",
      "doc_count" : 37
    }, {
      "key" : "holotype",
      "doc_count" : 6
    }, {
      "key" : "paratype",
      "doc_count" : 3
    }, {
      "key" : "isonéotype",
      "doc_count" : 2
    } ]
  },
  "D_TYPESTATUS_MISSING" : {
    "doc_count" : 0
  }
}

how to integret the "bool filter" in the "and filter" ?? thanks a lot

Answer 1

I must be missing something, because it's easy:

{
  "query": {
    "filtered": {
      "filter": {
        "and": [
          {
            "or": [
              {
                "term": {
                  "O_HASMEDIA": "true"
                }
              }
            ]
          },
          {
            "or": [
              {
                "term": {
                  "T_GENUS": "flemingia"
                }
              }
            ]
          },
          {
            "or": [
              {
                "term": {
                  "L_CONTINENT": "africa"
                }
              },
              {
                "term": {
                  "L_CONTINENT": "asia"
                }
              },
              {
                "missing": {
                  "field": "L_CONTINENT"
                }
              }
            ]
          },
          {
            "or": [
              {
                "term": {
                  "I_INSTITUTIONCODE": "mnhn"
                }
              }
            ]
          },
          {
            "bool": {
              "must_not": [
                {
                  "term": {
                    "D_TYPESTATUS": "type"
                  }
                }
              ],
              "must": {
                "exists": {
                  "field": "D_TYPESTATUS"
                }
              }
            }
          }
        ]
      }
    }
  }
}