DELETE

DQL language syntax for deleting documents

To remove a document from an Edge SDK device without permanently deleting it see EVICT.

When using DELETE ensure that devices running Ditto Edge SDK are on version 4.10.1 and later.

For more information on how to use delete and manage data reach out to Ditto’s Customer Support

When using DELETE on Edge SDKs-Only deployments reach out to Ditto’s Customer Support to ensure the best design patterns are being used to avoid data loss and/or performance related issues.

Deletes in Ditto requires all devices to connect and share information within the TTL window. If a device goes offline then appears later after all other devices have evicted the deleted document the connecting device will not know the document was deleted and share it with other peers.

Deletes in Ditto require all devices to connect and share updates within the configured TTL (time-to-live) window. Default 7 days (TOMBSTONE_TTL_HOURS) for Edge SDK, 30 days for Ditto Cloud.

If a device goes offline and returns after all other devices have already evicted the deleted document, that device will not be aware of the deletion. As a result, it may reintroduce the document to the sync network, causing it to reappear on other devices.

To learn more about how to best use DELETE in your application reach out to Ditto’s Customer Support

The DELETE operation permanently removes one or more documents from a Ditto collection. Once a document is deleted it cannot be recovered. Deleted documents can be re-created by using the INSERT operation with the same document id as the deleted document.

Syntax

DQL

DELETE FROM your_collection_name
USE IDS [ids]
WHERE [condition]
ORDER BY [order by]
LIMIT [limit]
OFFSET [offset]
RETURNING [projection]

In this syntax:

• your_collection_name is the name of the collection from which you want to retrieve the data.
• [ids] represents the document IDs to delete, given as a parameter or a literal list, rather than selecting documents with a WHERE predicate (optional).
• [condition] represents the condition or criteria that determine which documents should be evicted from the local peer.
• [order by] represents the path within a document to use to order the dataset and order (ASC or DESC).
• [limit] represents the maximum number of documents that should be evicted.
• [offset] represents the offset from 0 that should be used for the eviction query, in practice this should be rarely used.
• [projection] returns fields or expressions taken from the deleted documents (optional). See RETURNING.

Examples of Deleting Documents

Here, the document with ID 123 is permanently removed from the cars collection:

DQL

DELETE FROM cars
WHERE _id = '123'

In the following snippet, all documents created before the defined value are removed:

DQL

DELETE FROM cars
WHERE created_at < 1699888298000

Deleting Documents by ID

USE IDS on DELETE is available in SDK 5.1 and later.

The optional USE IDS clause names the documents to delete by ID, instead of selecting them with a WHERE predicate. The IDs may be supplied as a literal list or as a query parameter:

DQL

-- Literal list of IDs
DELETE FROM cars USE IDS '123', '456'

-- IDs supplied as a parameter
DELETE FROM cars USE IDS :ids

DELETE with RETURNING

RETURNING is available in SDK 5.1 and later.

Add a RETURNING clause to get back the deleted documents, as they were immediately before removal, rather than just the IDs of the documents that were deleted:

DQL

-- Return the complete deleted documents
DELETE FROM cars
USE IDS '123'
RETURNING *

-- Return selected fields of the deleted documents
DELETE FROM cars
WHERE created_at < 1699888298000
RETURNING _id, color

-- Return a count of the documents removed
DELETE FROM cars
WHERE created_at < 1699888298000
RETURNING COUNT(*) AS removed

Because the documents no longer exist once the statement completes, RETURNING is the only way to recover their contents. For the full projection rules, aggregate support and the restrictions that apply, see RETURNING.

Using DQL to Delete Documents in the Edge SDK

For specifics on deleting documents Ditto SDKs see SDK>CRUD>Removing Documents.

TOMBSTONE Keyword

The TOMBSTONE keyword (currently Ditto Server only) is a synonym for the new DELETE keyword. Users using the TOMBSTONE keyword will get the auto cleanup properties of DELETE without any changes. TOMBSTONE will be deprecated/removed in an upcoming Major release.

Removing Fields from Documents

The DELETE keyword is used to remove documents from a collection. To remove a specific field from a document see Update > Deleting Fields.

Removing a Collection

DQL does not provide a DROP COLLECTION statement, and the portal does not expose an action to delete a collection directly. Deleting every document in a collection is not equivalent to dropping it — the collection itself continues to exist after its documents are removed.

To empty a collection of its documents, run:

DQL

DELETE FROM your_collection_name

You can run this from the DQL Editor in the Data Browser and Editor, or from any SDK using store.execute.