elasticsearch - where fields is null

Question

I'm trying to write query (with foselastica bundle) where value should be some id or can be null.

In mysql: WHERE city.id = 1 OR city.id IS NULL

I know that I should use exists and must not expression, but not working for me. Any advice?

    $query = new \Elastica\Query();

    if ($phrase) {
        $queryString = new \Elastica\Query\QueryString($phrase);
        $query->setQuery($queryString);
    }

    $filter = new \Elastica\Query\BoolQuery();

    $city = new \Elastica\Query\Match();
    $city->setFieldQuery('city.id', $cityId);
    $filter->addShould($city);

    $nullCity = new \Elastica\Query\Exists('city.id');
    $filter->addMustNot($nullCity);

    $query->setPostFilter($filter);

Read [Dealing with null and not null values in elasticsearch queries](http://www.inanzzz.com/index.php/post/ioyu/dealing-with-null-and-not-null-values-in-elasticsearch-queries). You need to use [missing](https://www.elastic.co/guide/en/elasticsearch/guide/current/_dealing_with_null_values.html). — BentCoder, Jul 19 '17 at 09:11
missing query was removed from version 5. I'v got `no [query] registered for [missing]` exception. I can't find any useful example. — pan_slaw, Jul 19 '17 at 12:26

Elyass · Answer 1 · 2017-07-18T14:22:32.203

4

You're close to it. Try something like

$query = new \Elastica\BoolQuery();
// ...
$filter = new \Elastica\Query\BoolQuery();

$city = new \Elastica\Query\Match();
$city->setFieldQuery('city.id', $cityId);
$filter->addShould($city);

$nullCity = new BoolQuery();
$existQuery = new \Elastica\Query\Exists('city.id');
$nullCity->addMustNot($existQuery);
$filter->addShould($nullCity);

$query->addMust($filter);

edited Jul 18 '17 at 14:22

answered Jul 18 '17 at 13:27

Elyass

726
8
15

Thanks, but I can't `addMust()` on `$query`: `Attempted to call an undefined method named \"addMust\" of class \"Elastica\\Query\".` – pan_slaw Jul 18 '17 at 13:37
Except that I get `no [query] registered for [missing]` – pan_slaw Jul 18 '17 at 13:40
My bad, $query needs to be a BoolQuery. `$query = new \Elastica\Query\BoolQuery();` – Elyass Jul 18 '17 at 13:41
my `$filter` is `\Elastica\Query\BoolQuery()` object, should I add another one? – pan_slaw Jul 18 '17 at 13:47
Yes, i think you can try. For the `Missing` query, it has been removed because it can be replaced by an exists query inside a must_not clause, like you said first. I was working on an older version of elastica. Check my updated answer – Elyass Jul 18 '17 at 14:18

elasticsearch - where fields is null

1 Answers1