_delete_by_query with timestamp

Elastic Stack Kibana
1

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.

2

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.

3

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

4

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:

5

@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
6

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

7

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"
        }
      }
    }
  }
}
8

Any ideas on what went wrong？

11

Sorry for the long delay (I just saw this bumped). No idea what could happen to you but I could index your docs and delete them with your query without issues

# create index
PUT discuss-318157
{
  "settings": {
    "number_of_replicas": 1
  },
  "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"
      }
    }
  }
}

# add data
POST discuss-318157/_bulk
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542H", "Module_type" : "osb", "case_number" : 33 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542H", "Module_type" : "e_manual", "case_number" : 50 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542H", "Module_type" : "vha", "case_number" : 66 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542L", "Module_type" : "osb", "case_number" : 17 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542L", "Module_type" : "e_manual", "case_number" : 51 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD542L", "Module_type" : "himalaya", "case_number" : 24 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD764", "Module_type" : "osb", "case_number" : 37 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD764", "Module_type" : "e_manual", "case_number" : 61 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD764", "Module_type" : "himalaya", "case_number" : 26 }
{"index": {}}
{ "timestamp" : "2022-11-04 10:57:41", "Vehicle_type" : "CD764", "Module_type" : "vha", "case_number" : 3 }

# count all
GET discuss-318157/_count

# count with query
GET discuss-318157/_count
{
   "query": {
    "match": {
      "timestamp":"2022-11-04 10:57:41"
      }
    }
}

# delete with query
POST discuss-318157/_delete_by_query
{
   "query": {
    "match": {
      "timestamp":"2022-11-04 10:57:41"
      }
    }
}

# clean up
DELETE discuss-318157

The delete_by_query gave me the expected response:

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

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.