Delete by query not working

Question

I'm trying to delete documents with a date that is lower than december 1st but it doesn't look like it actually deletes anything.

I tried using the delete by query API:

curl -XPOST "http://localhost:9200/mediadata/events/_delete_by_query" -d'
{
  "query": {
    "range": {
      "created_at": {
        "lt": "2016-12-01 00:00:00"
      }
    }
  }
}'

Or this syntax:

curl -XDELETE 'http://localhost:9200/mediadata/events/_query' -d ...

I obtain this kind of result:

{"_index":"mediadata","_type":"events","_id":"_delete_by_query","_version":10,"_shards":{"total":3,"successful":2,"failed":0},"created":false}

Thanks in advance.

EDIT: Here is the mapping:

{
    "mediadata": {
        "mappings": {
            "events": {
                "properties": {
                    "channels": {
                        "properties": {
                            "kdata": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "mail": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "md5": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "mobile": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "ssp": {
                                "type": "string",
                                "index": "not_analyzed"
                            }
                        }
                    },
                    "contents": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "created_at": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "editor": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "end": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "location": {
                        "type": "geo_point"
                    },
                    "message": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "price": {
                        "type": "double"
                    },
                    "quantity": {
                        "type": "long"
                    },
                    "query": {
                        "properties": {
                            "bool": {
                                "properties": {
                                    "filter": {
                                        "properties": {
                                            "range": {
                                                "properties": {
                                                    "created_at": {
                                                        "properties": {
                                                            "lt": {
                                                                "type": "string"
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "must": {
                                        "properties": {
                                            "match_all": {
                                                "type": "object"
                                            }
                                        }
                                    }
                                }
                            },
                            "filtered": {
                                "properties": {
                                    "filter": {
                                        "properties": {
                                            "range": {
                                                "properties": {
                                                    "created_at": {
                                                        "properties": {
                                                            "lt": {
                                                                "type": "string"
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "query": {
                                        "properties": {
                                            "match_all": {
                                                "type": "object"
                                            }
                                        }
                                    }
                                }
                            },
                            "range": {
                                "properties": {
                                    "created_at": {
                                        "properties": {
                                            "lt": {
                                                "type": "string"
                                            },
                                            "lte": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    },
                    "reference": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "source": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "start": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "type": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "updated_at": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    }
                }
            }
        }
    }

}

Answer 1

Your syntax is indeed correct. In version 5.x the deletion by query is as follow .

POST mediadata/events/_delete_by_query?conflicts=proceed
{
  "query": {
    "range": {
      "created_at": {
        "gt": "2016-11-02 00:00:00"
      }
    }
  }
}

Now , based on the response that you're getting from ES

{"_index":"mediadata","_type":"events","_id":"_delete_by_query","_version":10,"_shards":{"total":3,"successful":2,"failed":0},"created":false}

I will assume that you're running version 2.x , where the syntax is different.

First of all , in version 2.x the deletion by query is a plugin that you need to install using :

plugin install delete-by-query

Then you run it :

curl -XDELETE "http://localhost:9200/mediadata/events/_query" -d'
{
  "query": {
    "range": {
      "created_at": {
        "gt": "2016-11-02 00:00:00"
      }
    }
  }
}'

The response looks like :

{
  "took": 0,
  "timed_out": false,
  "_indices": {
    "_all": {
      "found": 1,
      "deleted": 1,
      "missing": 0,
      "failed": 0
    },
    "mediadata": {
      "found": 1,
      "deleted": 1,
      "missing": 0,
      "failed": 0
    }
  },
  "failures": []
}

Full example :

PUT mediadata
{
    "mappings": {
            "events": {
                "properties": {
                    "channels": {
                        "properties": {
                            "kdata": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "mail": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "md5": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "mobile": {
                                "type": "string",
                                "index": "not_analyzed"
                            },
                            "ssp": {
                                "type": "string",
                                "index": "not_analyzed"
                            }
                        }
                    },
                    "contents": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "created_at": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "editor": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "end": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "location": {
                        "type": "geo_point"
                    },
                    "message": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "price": {
                        "type": "double"
                    },
                    "quantity": {
                        "type": "long"
                    },
                    "query": {
                        "properties": {
                            "bool": {
                                "properties": {
                                    "filter": {
                                        "properties": {
                                            "range": {
                                                "properties": {
                                                    "created_at": {
                                                        "properties": {
                                                            "lt": {
                                                                "type": "string"
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "must": {
                                        "properties": {
                                            "match_all": {
                                                "type": "object"
                                            }
                                        }
                                    }
                                }
                            },
                            "filtered": {
                                "properties": {
                                    "filter": {
                                        "properties": {
                                            "range": {
                                                "properties": {
                                                    "created_at": {
                                                        "properties": {
                                                            "lt": {
                                                                "type": "string"
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "query": {
                                        "properties": {
                                            "match_all": {
                                                "type": "object"
                                            }
                                        }
                                    }
                                }
                            },
                            "range": {
                                "properties": {
                                    "created_at": {
                                        "properties": {
                                            "lt": {
                                                "type": "string"
                                            },
                                            "lte": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    },
                    "reference": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "source": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "start": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    },
                    "type": {
                        "type": "string",
                        "index": "not_analyzed"
                    },
                    "updated_at": {
                        "type": "date",
                        "format": "yyyy-MM-dd' 'HH:mm:ss"
                    }
                }
            }
        }
}

PUT mediadata/events/1
{
  "created_at" : "2016-11-02 00:00:00"
}


PUT mediadata/events/3
{
  "created_at" : "2016-11-03 00:00:00"
}

#The one to delete
PUT mediadata/events/4
{
  "created_at" : "2016-10-03 00:00:00"
}

#to verify that the documents are in the index
GET mediadata/events/_search
{
  "query": {
    "range": {
      "created_at": {
        "lt": "2016-11-02 00:00:00"
      }
    }
  }
}

DELETE /mediadata/events/_query
{
  "query": {
    "range": {
      "created_at": {
        "gt": "2016-11-02 00:00:00"
      }
    }
  }
}