# Query for fields that don't contain a certain character **URL:** \ **Category:** Elasticsearch\ **Created:** [November 11, 2012, 2:15pm UTC](https://discuss.elastic.co/t/query-for-fields-that-dont-contain-a-certain-character/9663 "2012-11-11T14:15:32Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Marc\_Seeger\_2](https://avatars.discourse-cdn.com/v4/letter/m/d6d6ee/32.png) [@Marc\_Seeger\_2](https://discuss.elastic.co/u/Marc_Seeger_2)\ **Post date:** [November 11, 2012, 2:15pm UTC](https://discuss.elastic.co/t/query-for-fields-that-dont-contain-a-certain-character/9663/1 "2012-11-11T14:15:32Z")
I have a bit of dirty data in my index. While for all regular documents, the 'id' field should be a domainname ([example.com](http://example.com), [blog.example.com](http://blog.example.com), ...), there was a bit of bad code which inserted autogenerated values ('rM8CDN-aTC6lxaPIc858Rg', 'rYmT2kCvR9qoNaudg8\_2Wg') Now I'd like to write a little cleanup script that deletes these bad documents. My problem is that I have about 100 million documents, so just iterating over all of them would take ages. Something that I'd love to be able to do: Filter for id fields that don't have a dot in them. Would that need a wildcard query with a trailing and leading \*? Alternatively I could probably also filter for id fields that are 22 characters long. My problem is that I haven't figured out how to do either of those two things. Any recommendations on how to clean this up? -- ---
**Author:** ![simonw\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/simonw_2/32/1130_2.png) [@simonw\_2](https://discuss.elastic.co/u/simonw_2)\ **Post date:** [November 12, 2012, 8:38am UTC](https://discuss.elastic.co/t/query-for-fields-that-dont-contain-a-certain-character/9663/2 "2012-11-12T08:38:47Z")
Hey mark, unfortunately I don't see a way to do this efficiently and / or without major effort. The only way I can see to be reasonable is to write some custom lucene code that prunes your index but that would require to make your index read - only and shutdown ES which seems not reasonable either. I'd guess the easiest way is to reindex into another index do the work on the client side and don't index those docs in the first place. simon On Sunday, November 11, 2012 3:15:32 PM UTC+1, Marc Seeger wrote: > I have a bit of dirty data in my index. > While for all regular documents, the 'id' field should be a domainname ( > [example.com](http://example.com), [blog.example.com](http://blog.example.com), ...), there was a bit of bad code which > inserted autogenerated values ('rM8CDN-aTC6lxaPIc858Rg', > 'rYmT2kCvR9qoNaudg8\_2Wg') > > Now I'd like to write a little cleanup script that deletes these bad > documents. > My problem is that I have about 100 million documents, so just iterating > over all of them would take ages. > > Something that I'd love to be able to do: Filter for id fields that don't > have a dot in them. Would that need a wildcard query with a trailing and > leading \*? > Alternatively I could probably also filter for id fields that are 22 > characters long. > > My problem is that I haven't figured out how to do either of those two > things. > > Any recommendations on how to clean this up? -- ---
**Author:** ![Marc\_Seeger\_2](https://avatars.discourse-cdn.com/v4/letter/m/d6d6ee/32.png) [@Marc\_Seeger\_2](https://discuss.elastic.co/u/Marc_Seeger_2)\ **Post date:** [November 12, 2012, 11:33am UTC](https://discuss.elastic.co/t/query-for-fields-that-dont-contain-a-certain-character/9663/3 "2012-11-12T11:33:01Z")
In that case I'll probably just iterate over all of the IDs using the "scan" search type with the scroll parameter. Cheers, Marc On Monday, November 12, 2012 9:38:47 AM UTC+1, simonw wrote: > Hey mark, > > unfortunately I don't see a way to do this efficiently and / or without > major effort. The only way I can see to be reasonable is to write some > custom lucene code that prunes your index but that would require to make > your index read - only and shutdown ES which seems not reasonable either. > I'd guess the easiest way is to reindex into another index do the work on > the client side and don't index those docs in the first place. > > simon > > On Sunday, November 11, 2012 3:15:32 PM UTC+1, Marc Seeger wrote: > > > I have a bit of dirty data in my index. > > While for all regular documents, the 'id' field should be a domainname ( > > [example.com](http://example.com), [blog.example.com](http://blog.example.com), ...), there was a bit of bad code which > > inserted autogenerated values ('rM8CDN-aTC6lxaPIc858Rg', > > 'rYmT2kCvR9qoNaudg8\_2Wg') > > > > Now I'd like to write a little cleanup script that deletes these bad > > documents. > > My problem is that I have about 100 million documents, so just iterating > > over all of them would take ages. > > > > Something that I'd love to be able to do: Filter for id fields that don't > > have a dot in them. Would that need a wildcard query with a trailing and > > leading \*? > > Alternatively I could probably also filter for id fields that are 22 > > characters long. > > > > My problem is that I haven't figured out how to do either of those two > > things. > > > > Any recommendations on how to clean this up? -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:04am UTC](https://discuss.elastic.co/t/query-for-fields-that-dont-contain-a-certain-character/9663/4 "2017-07-06T03:04:45Z")