Delete documents by filter

[gmourier]

Hi everyone 👋

We would like your feedback about being able to delete documents matching the same filters.

How would you like to use it and for what purpose? Delete documents that have become obsolete? Delete documents that should not have been indexed?

Thanks for your feedback 😇

[mikkeschiren]

I really would love to have the possibility to delete documents that have become obsolete, one of my cases uses random id:s (there is no unique identifier for the objects), and then I would like to delete the content that matches some criteria, and then update with new documents.

[gmourier]

Thanks, @mikkeschiren! What is the discriminant to determine that a document has become obsolete in your use case? Would set a TTL at the time of adding the document have been useful as well?

[ozgurhangisi]

Hi,

In our case, our customers generally has product table with is_active field and we only want to keep active products on the index. We also have last_update_time field in the index. They don't delete passive products from the table for reporting reasons.

I know the best way is to send delta update but for most of the customers we have no ability to do that. We send active products to the index (not delta update we send all of them) and remove passive products. To remove passive products firstly we get last update starting date from our db then we send a search query to get productIds that has earlier last_update_time value than the last update starting date then we send delete requests to the index. If we had an ability to remove items by query it would be so much easier for us and other people to implement meili search on their system.

Because the hardest part of the implementation for us is to keep the index up to date.

[sandstrom]

We use ElasticSearch today, and a common operation is to delete all records for a particular company-id. So basically, it's a multi-tenant app and when a customer account is deleted, we make a query to delete all records in the index that match the filter/facet, e.g. company_id == 3.

[moy2010]

My use case for this feature is for partial reindexing. In certain situations, we know that a subset of the index has gone out-of-sync, and the easiest solution is to delete all documents in the index that match a given filter (i.e. category = books) right before reading the documents.

Why do we need to delete and not just update? Because a document that exists in the index might no longer exist in the database (this is what we consider going out-of-sync).

[thijndehaas]

We are currently also using this for a project that is still running on ElasticSearch and is being converted to Meilisearch. We may import duplicate data from external suppliers and merge them. Here we could first remove all products with the same unique identifier so that everything is definitely gone and then create a record for the merged product. At the moment we fetch all IDs from MySQL and then delete the Meilisearch records by ID.

[gmourier]

Hi @thijndehaas 👋

How would you use a delete by query with Meilisearch for your use-case?

Do you have a detailed example? (Let's say it's possible to use filter and q on DELETE /documents)

Thank you!

[thijndehaas]

For example if a partner of us stops the partnership and we want to remove all their products we could just filter by the partner_id without looping all products.
Another example could be to filter by an ean column and remove all products with that EAN.

This is not something we have as a requirement, it could just speed up some tasks.

[gmourier]

Thanks @thijndehaas !

[elidrissidev]

Hello all,

I wanted to post about this, but I noticed it already existed!

For me, I have an index of products, the same product can exist in multiple stores. Since I can't use the same ID in multiple documents, I set the document id to be <product_id>/<store_id>, like so:

[{ "id": "123/1", "product_id": 123, "store_id": 1 },
{ "id": "123/2", "product_id": 123, "store_id": 2 }]

Now for my use-case, if I want to delete store 2 and all of its products, there is no way to fetch the products in that specific store, and so I would need to manually query all the products in that store and then do a batch delete, or perform a complete re-indexation.

I would like if there was ability to perform filtering when deleting documents.

Thanks.

[curquiza]

Hello everyone watching this discussion: we've created an issue on the Meilisearch repo about this feature meilisearch/meilisearch#2787
This issue will be available for Hacktoberfest. If anyone wants to do a Rust contribution, they are more than welcome, the core team will be available to help you and review your PR 😄

[mmachatschek]

We are using distinct fields on some indexes where there are multiple representations of a record.
I hope that this feature will remove all entries in the index that matches the criteria ignoring the distinct field attribute.

cc @dureuill (I guess you are implementing this in meilisearch/meilisearch#3477)

[gmourier]

Thank you for mentioning this aspect, @mmachatschek!

There is little chance this new feature will be present for v1.1; in that case, it will be shifted to v1.2 to ensure we move forward on the highest priorities we have determined!

[dureuill]

Hello @mmachatschek 👋

Thank you for raising this point, it is very valid. Can you imagine a use case where you would still take the distinct field attribute into account, or would it always be good to ignore it when deleting with a filter?

I think I'll begin working on this feature soon but as @gmourier mentioned, it is likely to miss the train for v1.1 at this point.

[mmachatschek]

Can you imagine a use case where you would still take the distinct field attribute into account, or would it always be good to ignore it when deleting with a filter?

I can't imagine a use case where it would make sense 🤔 the distinct field should only be accounted on the regular search endpoints

I think I'll begin working on this feature soon but as @gmourier mentioned, it is likely to miss the train for v1.1 at this point.

no worries, I didn't expect any rush here towards the 1.1 release 😅 just wanted to point out my concern here as I saw the issue meilisearch/meilisearch#3477 on the other repo.

[candiduslynx]

In the Cloudquery Meilisearch source plugin we weren't able to implement the overwrite-delete-stale functionality.

The reason for this is that currently we would have to read all index documents and then delete the documents by UID (having parsed & checked the _cq_sync_time & _cq_source_name values in the plugin).

This feature would allow us filtering by _cq_sync_time & _cq_source_name attributes and delete the obsolete documents right away.

cc: @hermanschaaf @yevgenypats

[intoxicated]

fyi, its in progress https://roadmap.meilisearch.com/c/103-delete-documents-by-filters

[macraig]

Hi everyone! 👋 😄

We wanted to give you an update on the status of this feature. We're currently working on a prototype (which will be available in the next few weeks) that will allow you to delete documents by filterableAttributes. This means that you'll have to make sure that the attributes you use as deletion criteria are in the filterableAttributes list. Does this sound like a viable solution for your use cases?

[candiduslynx]

This is absolutely the implementation we're expecting.

[moy2010]

This is absolutely the implementation we're expecting.

Same for me. It's exactly how I thought it was going to be implemented 🥳

[macraig]

Thank you all for the fast replies! 🙏 It's great to know that this solution fits your needs, we'll keep you posted on our progress!

[roelofjan-elsinga]

This behavior is exactly as I would it expect to work, so I think you're on the right path with this solution!

[macraig]

Thank you for your feedback @roelofjan-elsinga! 🙇

[macraig]

Hi everyone 👋

As promised, here's a 🧪 prototype that allows you to delete your documents by filter.

How to get the prototype?

Using docker, use the following command:

docker run -p 7700:7700 -v $(pwd)/meili_data:/meili_data getmeili/meilisearch:prototype-delete-by-filter-0

How to use the prototype?

A new route is available under POST /indexes/{index_uid}/documents/delete that allows you to delete your documents by filter.
The expected payload looks like this:

{
  "filter": "doggo = bernese",
}

It'll then enqueue a task in your task queue that'll delete all the documents matching this filter once it's processed.
Here is an example of the associated details;

  "details": {
    "deletedDocuments": 53,
    "originalFilter": "\"doggo = bernese\""
  }

You can also check out the original PR for more info

⚠️ We do NOT recommend using this prototype in production. This is only for test purposes.
Everyone is more than welcome to give feedback and to report any issue or bug you might encounter when using this prototype. Thanks in advance for your involvement. It means a lot to us ❤️

[candiduslynx]

@Kerollmops unfortunately, there are issues with nanosecond precision in the Unix timestamp solution.

To go over this in more details:
Consider 2023-04-28 15:50:34.671067000 (unix: 1682697034671067000).
If I follow the unix conversion and store 1682697034671067000 it'll be unmarshaled as 1682697034671066880 which is not the same value.
The reason for this is usage of float64 underneath.
However, storing and unmarshaling "2023-04-28 15:50:34.671067000" works like a charm.

Even the MarshalJSON func for time.Time utilizes RCF3390Nano for this specific reason.

So, it'd be a no-go for us at the moment.

[Kerollmops]

Hey @candiduslynx,

Another, maybe better, solution would probably be to store the nanosecond number as a string and with 0 front-padding. This will make sure that the string is always the same size and that there is probably less underneath issues with using the string format you use.

In my example, this 1682697034671067000 would be stored as 00000000001682697034671067000 (with the appropriate number of zeros).

[candiduslynx]

@Kerollmops

store the nanosecond number as a string and with 0 front-padding

Yeah, but then why do I need the string with padding when I can have the RFC3390Nano that already provides the proper timestamp order?
The issue I have here is that the strings don't allow < operator, and storing the "00000000001682697034671067000" value wouldn't solve this problem.

Would you consider adding < and > support (as well as != if it's not yet there) to the string values instead?

[gmourier]

Hey @candiduslynx 👋

Can you create a dedicated product discussion for that so we can discuss it in a precise spot making it more visible for other people to vote for it and share their needs; It would be super appreciated 🙇‍♂️

Thanks!

[candiduslynx]

@gmourier I think there's already one: #639

[macraig]

Hello everyone 👋

We have just released the first RC (release candidate) of Meilisearch containing this new feature!
You can test it by using

• the release assets
• the Meilisearch Docker image

docker run -it --rm -p 7700:7700 -v $(pwd)/meili_data:/meili_data getmeili/meilisearch:v1.2.0-rc.0

You are welcome to leave your feedback in this discussion.

If you encounter any bugs, please report them here.
Thanks in advance for your help and your involvement in Meilisearch ❤️

🎉 Official and stable release containing this change will be available on June 5th, 2023

⚠️ RC (release candidates) are not recommended for production

[hspak]

For the new document deletion by filter feature, does this perform any sort of batching to the task?

e.g if the filter matches 1 million documents, will meilisearch attempt to batch these deletes? Or is the number of documents getting deleting in a single task not a concern?

[irevoire]

Hey @hspak,
Deleting millions of documents in a single task is not an issue for meilisearch 😁

[macraig]

Hey folks 👋

v1.2 has been released! 🐻‍❄️ You can now Get and Delete documents by filter. ✨

Note: Fields must be set as filterable before using them as filters.

📚 https://www.meilisearch.com/docs/reference/api/documents#delete-documents-by-filter
📚 https://www.meilisearch.com/docs/reference/api/documents#get-documents-with-post