UPDATE - Ditto

With an UPDATE statement, you can update specific fields within the documents based on specified conditions:

DQL

UPDATE your_collection_name
[APPLY field INCREMENT BY value | field RESTART [WITH value], ...]
[SET field1 = value1, field2.subfield = value2, ...]
[UNSET field3, field4, ...]
[WHERE condition]
[ORDER BY expression [ASC|DESC], ...]
[LIMIT limit_value]
[OFFSET offset_value]

In this syntax:

your_collection_name is the name of the collection in which you want to update data.
APPLY clause is used for counter operations (optional)
SET clause specifies fields to be updated and their corresponding new values (optional)
UNSET clause specifies fields to be deleted (optional)
WHERE clause filters which documents to update (optional)
ORDER BY clause controls the order in which documents are updated (optional)
LIMIT clause restricts the number of documents updated (optional)
OFFSET clause skips a number of documents before updating (optional)

At least one of APPLY, SET, or UNSET must be specified in an UPDATE statement.

Basic UPDATE

Here is an example of a basic UPDATE operation:

DQL

UPDATE your_collection_name
SET field1 = 'blue'
WHERE _id = '123'

UPDATE Multiple Fields

The following snippet shows an example of using UPDATE to set multiple fields:

DQL

UPDATE your_collection_name
SET
  field1 = 'blue',
  field2 = 0
WHERE _id = '123'

UPDATE with Nested Fields

Dot notation is available in 4.11 and later, with DQL_STRICT_MODE=false. Read more

When updating fields nested in a MAP, specify the field-value pairs you want to update. For MAP syntax, see Ditto Query Language > Types and Definitions > Map Operations.

UPDATE your_collection_name 
SET
  field2.sub1  = 2,
  field2.sub2 = 'spring'
WHERE field1 = 'red'

-- Arrow functions (->) have been removed in v5
UPDATE COLLECTION your_collection_name (field2 MAP)
SET
  field2 -> (
     sub1 = 2,
     sub2 = 'spring'
  )
WHERE field1 = 'red'

UPDATE with deserialize_json

Starting with SDK 4.8, you can use the deserialize_json() function in UPDATE statements to set fields from JSON-serialized strings. This is useful when you receive data as JSON strings (for example, from an API response) and want to update specific fields on existing documents.

Update a single field from a JSON string

You can deserialize a JSON string and use it to set a field value:

await ditto.store.execute(
  query: """
    UPDATE cars
    SET properties = deserialize_json(:jsonData)
    WHERE _id = '123'
    """,
  arguments: [ "jsonData": "{\"color\": \"red\", \"mileage\": 5000}" ])

ditto.store.execute("""
  UPDATE cars
  SET properties = deserialize_json(:jsonData)
  WHERE _id = '123'
  """,
  mapOf("jsonData" to "{\"color\": \"red\", \"mileage\": 5000}"))

await ditto.store.execute(`
  UPDATE cars
  SET properties = deserialize_json(:jsonData)
  WHERE _id = '123'`,
  { jsonData: '{"color": "red", "mileage": 5000}' });

ditto.store.execute(
    "UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
    Map.of("jsonData", "{\"color\": \"red\", \"mileage\": 5000}"));

var args = new Dictionary<string, string>();
args.Add("jsonData", "{\"color\": \"red\", \"mileage\": 5000}");

await ditto.Store.ExecuteAsync(
  "UPDATE cars"
+ " SET properties = deserialize_json(:jsonData)"
+ " WHERE _id = '123'",
  args);

std::map<std::string, std::string> args;
args["jsonData"] = "{\"color\": \"red\", \"mileage\": 5000}";

ditto.get_store().execute(
  "UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
  args).get();

let query_result = ditto
    .store()
    .execute_v2((
        "UPDATE cars SET properties = deserialize_json(:jsonData) WHERE _id = '123'",
        serde_json::json!({
            "jsonData": "{\"color\": \"red\", \"mileage\": 5000}"
        }),
    )).await?;

await ditto.store.execute("""
  UPDATE cars
  SET properties = deserialize_json(:jsonData)
  WHERE _id = '123'""",
  arguments: {"jsonData": "{\"color\": \"red\", \"mileage\": 5000}"},
);

Update multiple fields from a JSON string

You can also combine deserialize_json() with other SET assignments in the same UPDATE:

await ditto.store.execute(
  query: """
    UPDATE cars
    SET
      color = deserialize_json(:colorJson),
      mileage = 6000
    WHERE _id = '123'
    """,
  arguments: [ "colorJson": "\"red\"" ])

ditto.store.execute("""
  UPDATE cars
  SET
    color = deserialize_json(:colorJson),
    mileage = 6000
  WHERE _id = '123'
  """,
  mapOf("colorJson" to "\"red\""))

await ditto.store.execute(`
  UPDATE cars
  SET
    color = deserialize_json(:colorJson),
    mileage = 6000
  WHERE _id = '123'`,
  { colorJson: '"red"' });

ditto.store.execute(
    "UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
    Map.of("colorJson", "\"red\""));

var args = new Dictionary<string, string>();
args.Add("colorJson", "\"red\"");

await ditto.Store.ExecuteAsync(
  "UPDATE cars"
+ " SET color = deserialize_json(:colorJson), mileage = 6000"
+ " WHERE _id = '123'",
  args);

std::map<std::string, std::string> args;
args["colorJson"] = "\"red\"";

ditto.get_store().execute(
  "UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
  args).get();

let query_result = ditto
    .store()
    .execute_v2((
        "UPDATE cars SET color = deserialize_json(:colorJson), mileage = 6000 WHERE _id = '123'",
        serde_json::json!({
            "colorJson": "\"red\""
        }),
    )).await?;

await ditto.store.execute("""
  UPDATE cars
  SET
    color = deserialize_json(:colorJson),
    mileage = 6000
  WHERE _id = '123'""",
  arguments: {"colorJson": "\"red\""},
);

Deleting Fields

UNSET is available in 4.11 and later.

In Ditto, fields need to be marked as “deleted” for other peers to know the field has been removed.

When unsetting a MAP, all children data types are iteratively unset.
Fields that are unset are ignored during subsequent DQL statements.
Calling UNSET on a large number of dynamically generated fields (for example, dynamically created keys in a CRDT map) may cause performance to degrade due to metadata accumulation over time. Benchmarks for this will vary depending on your dataset size and query cardinality. You can mitigate this accumulation by calling UNSET on a parent field (or deleting the document itself).

UPDATE your_collection_name
UNSET field_name 
WHERE _id = '123'

-- Arrow (->) and tombstone() functions have been removed in v5
 UPDATE your_collection_name
 SET your_field_name -> tombstone()
 WHERE _id = '123'

APPLY Clause for Counters

Counters are available in SDK 4.11+ (PN_COUNTER) and 4.13+ (COUNTER). See Counter Types for details.

The APPLY clause is used to perform counter operations on COUNTER or PN_COUNTER fields:

DQL

UPDATE COLLECTION collection_name (counter_field COUNTER)
APPLY counter_field INCREMENT BY value
WHERE [condition]

INCREMENT BY

Increment or decrement a counter (use negative values to decrement):

DQL

-- Increment counter
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 5
WHERE _id = '123'

-- Decrement counter
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY -3
WHERE _id = '123'

RESTART (COUNTER only)

Reset a counter to zero or set it to a specific value:

DQL

-- Reset to zero
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART
WHERE _id = '123'

-- Set to specific value
UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count RESTART WITH 100
WHERE _id = '123'

Combining APPLY with SET

You can combine counter operations with regular field updates:

DQL

UPDATE COLLECTION products (stock_count COUNTER)
APPLY stock_count INCREMENT BY 10
SET lastUpdated = '2025-12-16', updatedBy = 'user123'
WHERE _id = '123'

ORDER BY, LIMIT, and OFFSET

UPDATE statements support ORDER BY, LIMIT, and OFFSET clauses to control which documents are updated and in what order.

ORDER BY

Control the order in which documents are processed for update:

DQL

-- Update oldest items first
UPDATE inventory
SET status = 'archived'
WHERE lastActivity < '2024-01-01'
ORDER BY lastActivity ASC
LIMIT 100

LIMIT

Restrict the number of documents updated:

DQL

-- Update only the first 10 matching documents
UPDATE products
SET featured = true
WHERE category = 'electronics' AND rating > 4.5
LIMIT 10

OFFSET

Skip a number of documents before updating:

DQL

-- Skip first 20 documents, then update next 10
UPDATE tasks
SET priority = 'low'
WHERE status = 'pending'
ORDER BY createdAt DESC
OFFSET 20
LIMIT 10

Combining ORDER BY, LIMIT, and OFFSET

These clauses work together to provide fine-grained control:

DQL

-- Update the 10 most expensive items after the first 5
UPDATE products
SET discount = 0.2
WHERE category = 'luxury'
ORDER BY price DESC
OFFSET 5
LIMIT 10

ORDER BY, LIMIT, and OFFSET are evaluated after the WHERE clause filters documents. This means:

Documents are first filtered by the WHERE condition
Results are then ordered by ORDER BY
OFFSET skips documents from the ordered results
LIMIT restricts how many documents are updated

​Basic UPDATE

​UPDATE Multiple Fields

​UPDATE with Nested Fields

​UPDATE with deserialize_json

​Update a single field from a JSON string

​Update multiple fields from a JSON string

​Deleting Fields

​APPLY Clause for Counters

​INCREMENT BY

​RESTART (COUNTER only)

​Combining APPLY with SET

​ORDER BY, LIMIT, and OFFSET

​ORDER BY

​LIMIT

​OFFSET

​Combining ORDER BY, LIMIT, and OFFSET

Basic UPDATE

UPDATE Multiple Fields

UPDATE with Nested Fields

UPDATE with deserialize_json

Update a single field from a JSON string

Update multiple fields from a JSON string

Deleting Fields

APPLY Clause for Counters

INCREMENT BY

RESTART (COUNTER only)

Combining APPLY with SET

ORDER BY, LIMIT, and OFFSET

ORDER BY

LIMIT

OFFSET

Combining ORDER BY, LIMIT, and OFFSET