Elastic Search deleteByQuery multiple terms

Question

I'm having trouble duplicating my MySQL delete query in elastic search, I am using this documentation: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/docs-delete-by-query.html using the PHP wrapper for Laravel.

I'm trying this:

$this->es->deleteByQuery([
    'index' => 'users',
    'type'  => 'user',
    'body'  => [
        'query' => [
                            'term' => ['field1' => $this->field1],
                            'term' => ['field2' => $this->field2],
                            'term' => ['temp' => 0]
        ]
    ]
]);

Its suppose to be a DELETE FROM users WHERE field1 = $this->field1 AND field2 = $this->field2...

I'm having trouble translating the WHERE AND syntax to Elastic Search.

Any help?

I think I have have gone overboard right now. I have body => query => filter => filtered => bool => must => term, term, term. Do I need the filter => filtered arrays? — Anthony
you don't need filter. there is a bool query without filter: elasticsearch.org/guide/en/elasticsearch/reference/current/… — Ashalynd

Zach Zach · Accepted Answer · 2014-12-16T11:45:38

Your second comment was mostly correct:

I think I have have gone overboard right now. I have body => query => filter => filtered => bool => must => term, term, term. Do I need the filter => filtered arrays?

The bool filter is preferable over the bool query, since filtering is often much faster than querying. In your case, you are simply filtering documents that have the various terms, and don't want them contributing to the score, so filtering is the correct approach.

This should be done though the query clause, however, since the top-level filter clause is used for a different purpose (filtering facets/aggregations...it was in-fact renamed to post_filter in 1.0 to signify that it is a "post filtering" operation).

Your query should look something like this:

$this->es->deleteByQuery([
    'index' => 'users',
    'type'  => 'user',
    'body'  => [
        'query' => [
            'filtered' => [
                'filter' => [
                    'must' => [
                        ['term' => ['field1' => $this->field1]],
                        ['term' => ['field2' => $this->field2]],
                        ['term' => ['temp' => 0]]
                    ]
                ]
            ]       
        ]
    ]
]);

Elastic Search deleteByQuery multiple terms

1 Answers