_delete_by_query with timestamp

The data I uploaded to Elasticsearch database is like the following

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 35,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "xgWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542H",
          "Module_type" : "osb",
          "case_number" : 33
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "xwWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542H",
          "Module_type" : "e_manual",
          "case_number" : 50
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "yAWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542H",
          "Module_type" : "vha",
          "case_number" : 66
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "yQWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542L",
          "Module_type" : "osb",
          "case_number" : 17
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "ygWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542L",
          "Module_type" : "e_manual",
          "case_number" : 51
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "ywWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD542L",
          "Module_type" : "himalaya",
          "case_number" : 24
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "zAWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD764",
          "Module_type" : "osb",
          "case_number" : 37
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "zQWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD764",
          "Module_type" : "e_manual",
          "case_number" : 61
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "zgWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD764",
          "Module_type" : "himalaya",
          "case_number" : 26
        }
      },
      {
        "_index" : "yiming_test",
        "_type" : "_doc",
        "_id" : "zwWWQIQBSvMJDRAYpLCl",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2022-11-04 10:57:41",
          "Vehicle_type" : "CD764",
          "Module_type" : "vha",
          "case_number" : 3
        }
      }
    ]
  }
}

The timestamp is in the format of "YY-mm-dd HH:MM:SS"

If I want to fetch the data stored in the index, I use

GET /yiming_test/_search
{
   "query": {
    "match": {
      "timestamp":"2022-11-04 10:57:41"
      }
    }
}

The response is fine, which is exact the same as the screenshot above shows.

However, when I want to delete the data by using

POST /yiming/_delete_by_query
{
   "query": {
    "match": {
      "timestamp":"2022-11-04 10:57:41"
      }
    }
}

No document has been deleted successfully.
The response I got is as the follows with the response code 200

{
  "took" : 0,
  "timed_out" : false,
  "total" : 0,
  "deleted" : 0,
  "batches" : 0,
  "version_conflicts" : 0,
  "noops" : 0,
  "retries" : {
    "bulk" : 0,
    "search" : 0
  },
  "throttled_millis" : 0,
  "requests_per_second" : -1.0,
  "throttled_until_millis" : 0,
  "failures" : [ ]
}

Could you please tell me what I can do to delete the document based on the timestamp?

Any help is appreciated.

Welcome to our community! :smiley:

Does not match

There's 1 second difference. And while it's hard to read your screenshot (please just post the code in future!), there doesn't appear to be any "10:57:42" timestamps.

sorry, my bad, I changed to


POST /yiming/_delete_by_query/
{
   "query": {
    "match": {
      "timestamp":"2022-11-04 10:57:41"
      }
    
    }
}

still, I got nothing deleted.

{
  "took" : 0,
  "timed_out" : false,
  "total" : 0,
  "deleted" : 0,
  "batches" : 0,
  "version_conflicts" : 0,
  "noops" : 0,
  "retries" : {
    "bulk" : 0,
    "search" : 0
  },
  "throttled_millis" : 0,
  "requests_per_second" : -1.0,
  "throttled_until_millis" : 0,
  "failures" : [ ]
}

By the way I didn't write any code to do these CRUD operations, I just used the dev tools

It's still code. Pictures of text, logs or code are difficult to read, impossible to search and replicate (if it's code), and some people may not be even able to see them :slight_smile:

@warkolm , Thank you for your advice, I modified the posts a bit and pasted the code instead of the screenshot. Please help me have a quick check

1 Like

Thank you, can you show the output from GET yiming/_mapping please?

Sure, the response is:

{
  "yiming_test" : {
    "mappings" : {
      "properties" : {
        "Module_type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "Vehicle_type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "case_number" : {
          "type" : "long"
        },
        "timestamp" : {
          "type" : "date",
          "format" : "yyyy-MM-dd HH:mm:ss"
        }
      }
    }
  }
}

Any ideas on what went wrong?