_delete_by_query with timestamp

Yiming_Gong · November 4, 2022, 5:03am

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.

warkolm · November 4, 2022, 5:28am

Welcome to our community!

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.

Yiming_Gong · November 4, 2022, 5:33am

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

warkolm · November 4, 2022, 5:44am

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

Yiming_Gong · November 4, 2022, 5:53am

@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

warkolm · November 4, 2022, 5:54am

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

Yiming_Gong · November 4, 2022, 5:56am

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

Yiming_Gong · November 4, 2022, 7:26am

Any ideas on what went wrong？

jsanz · December 30, 2022, 3:46pm

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": []
}

system · January 27, 2023, 3:46pm

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