SQL++ data modification, functions & transactions

This section documents the current Couchbase Server 7.6.x line as published at the Couchbase Server documentation, which is the reference these pages are written and verified against. No specific patch version is pinned. Some capabilities (Enterprise-Edition-only Analytics, auditing, encryption at rest, the Backup service and rack-zone awareness, and Capella-only App Services and Columnar) are linked, not documented in depth.

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production, as Couchbase iterates quickly.

This section’s bibliography lists the reference material consulted while preparing these pages.

This page covers the write side of SQL++: the DML statements that change documents, the built-in and user-defined function library, and the distributed ACID transactions that let one unit of work span many documents and keyspaces.

Data modification: INSERT, UPSERT, UPDATE, DELETE, MERGE

Every SQL++ mutation identifies documents by key. INSERT and UPSERT take an explicit KEY / VALUE pair (or a SELECT that produces them); UPDATE, DELETE and MERGE locate documents by USE KEYS or by a WHERE predicate resolved through an index (DML statements).

-- INSERT fails if the key already exists
INSERT INTO main.sales.orders (KEY, VALUE)
VALUES ("order::o-100",
        { "type": "order", "userId": "u-1", "total": 42.00, "status": "OPEN" });

-- UPSERT writes or replaces, with no error on an existing key
UPSERT INTO main.sales.orders (KEY, VALUE)
VALUES ("order::o-100",
        { "type": "order", "userId": "u-1", "total": 42.00, "status": "OPEN" });

-- INSERT ... SELECT: derive the key and value from another keyspace
INSERT INTO main.sales.orderArchive (KEY k, VALUE o)
SELECT META(o).id AS k, o
FROM main.sales.orders AS o
WHERE o.status = "CLOSED";

SET and UNSET edit fields in place, including nested paths and array elements reached through a FOR clause; RETURNING echoes a projection of each mutated document:

UPDATE main.sales.orders AS o
USE KEYS "order::o-100"
SET o.status = "PAID",
    o.payment = { "method": "card", "at": NOW_STR() },
    o.lines[0].note = "gift wrap"
UNSET o.promoCode
RETURNING META(o).id, o.status;
-- predicate-driven UPDATE that rewrites array elements
UPDATE main.sales.orders AS o
SET l.price = l.price * 1.10 FOR l IN o.lines WHEN l.taxable = true END
WHERE o.status = "OPEN";
DELETE FROM main.sales.orders AS o
WHERE o.status = "ABANDONED" AND o.updatedAt < "2026-01-01"
RETURNING META(o).id;

MERGE applies updates, deletes and inserts to a target keyspace from a source in one statement:

MERGE INTO main.inventory.stock AS t
USING main.staging.delivery AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (KEY s.sku,
  VALUE { "type": "stock", "sku": s.sku, "qty": s.qty });

For the relational treatment of the same operations, see SQL Data Modification.

Functions

SQL++ ships a large built-in library (functions reference); the families that matter most for JSON:

  • Scalar — string (LOWER, SUBSTR, SPLIT, REGEXP_MATCHES), number (ROUND, TRUNC, ABS), date (NOW_STR, DATE_ADD_STR, DATE_DIFF_STR, MILLIS_TO_STR), type (TYPE, TONUMBER, TOSTRING, TOARRAY) and conditional (CASE, NVL, IFMISSING, IFMISSINGORNULL).

  • Array — ARRAY_LENGTH, ARRAY_APPEND, ARRAY_CONCAT, ARRAY_DISTINCT, ARRAY_FLATTEN, ARRAY_AGG, ARRAY_SUM, ARRAY_SORT.

  • Object — OBJECT_NAMES, OBJECT_VALUES, OBJECT_PAIRS, OBJECT_ADD, OBJECT_REMOVE, OBJECT_PUT.

  • Metadata and search — META() exposes id, cas, expiration and xattrs; SEARCH() and SEARCH_SCORE() push a predicate to a Full Text Search index from inside SQL++ — see Search, Analytics & Eventing.

SELECT META(h).id,
       META(h).cas,
       MILLIS_TO_STR(META(h).expiration * 1000) AS expires_at,
       ARRAY_LENGTH(h.public_likes) AS likes,
       OBJECT_NAMES(h.geo) AS geo_keys
FROM `travel-sample`.inventory.hotel AS h
LIMIT 3;
-- SEARCH() against an FTS index, scored and ordered
SELECT h.name, SEARCH_SCORE() AS score
FROM `travel-sample`.inventory.hotel AS h
WHERE SEARCH(h, "spa +country:France")
ORDER BY score DESC
LIMIT 10;

Aggregate and window functions. The aggregates (COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, MEDIAN, STDDEV) collapse a group; window functions compute a value per row over a moving frame without collapsing it (window functions). The OVER (PARTITION BY …​ ORDER BY …​) clause, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and running-aggregate frames all match SQL.

SELECT r.airline, r.sourceairport, r.distance,
       ROW_NUMBER() OVER (PARTITION BY r.airline ORDER BY r.distance DESC) AS rn,
       SUM(r.distance) OVER (PARTITION BY r.airline
                             ORDER BY r.distance DESC
                             ROWS UNBOUNDED PRECEDING) AS running_km
FROM `travel-sample`.inventory.route AS r
WHERE r.distance IS VALUED;

For the relational reference on both, see SQL Built-in Functions and SQL Aggregate & Window Functions.

User-defined functions extend the library (user-defined functions). An inline UDF wraps a SQL++ expression; an external UDF runs a JavaScript function stored in a library.

-- inline SQL++ UDF, scoped to a collection
CREATE OR REPLACE FUNCTION main.sales.order_total(order_doc) {
  (SELECT RAW SUM(l.price * l.qty) FROM order_doc.lines AS l)[0]
};

SELECT main.sales.order_total(o) AS total
FROM main.sales.orders AS o
USE KEYS "order::o-100";
-- external JavaScript UDF: the "discount" export of the "sales-lib" library
CREATE OR REPLACE FUNCTION main.sales.discount(price, pct)
  LANGUAGE JAVASCRIPT AS "discount" AT "sales-lib";

Distributed ACID transactions

Couchbase runs multi-document, multi-keyspace ACID transactions across the cluster (transactions). Two front ends drive the same engine.

SDK lambda API. transactions.run(ctx → { …​ }) takes a closure; the SDK retries the whole closure on a transient conflict and commits when it returns normally:

cluster.transactions().run(ctx -> {
    var from = ctx.get(accounts, "acct::a-1");
    var to   = ctx.get(accounts, "acct::a-2");
    var fromDoc = from.contentAsObject().put("balance",
                     from.contentAsObject().getInt("balance") - 100);
    var toDoc   = to.contentAsObject().put("balance",
                     to.contentAsObject().getInt("balance") + 100);
    ctx.replace(from, fromDoc);
    ctx.replace(to, toDoc);
    // no explicit commit: returning commits, throwing rolls back
});

SQL++ statements. BEGIN WORK (or SET TRANSACTION) opens one, SAVEPOINT and ROLLBACK TO SAVEPOINT mark partial undo points, COMMIT WORK / ROLLBACK WORK end it:

BEGIN WORK;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE main.bank.account USE KEYS "acct::a-1" SET balance = balance - 100;
SAVEPOINT s1;
UPDATE main.bank.account USE KEYS "acct::a-2" SET balance = balance + 100;

COMMIT WORK;

Isolation. Transactions provide Read Committed isolation: a statement never sees another transaction’s uncommitted writes, and reads inside the transaction see its own earlier writes.

How it works. Each transaction owns an Active Transaction Record (ATR) — one of a fixed set of up to 1024 ATR documents per collection that tracks transaction state (PENDING, COMMITTED, ABORTED). Each mutated document is staged in its extended attributes (XATTRs), not its body, with a pointer to the owning ATR. The single atomic write that flips the ATR entry to COMMITTED is the commit point; asynchronous cleanup then copies staged content into document bodies and removes the staging. A reader that meets a still-staged document consults the ATR and either ignores the stage (not yet committed) or reads the staged value forward (committed, cleanup pending).

stateDiagram-v2 [*] --> Pending: create ATR entry Pending --> Staged: write docs into XATTRs, ATR = PENDING Staged --> Staged: further mutations staged Staged --> Committed: atomic ATR flip to COMMITTED (commit point) Staged --> Aborted: conflict / error / explicit ROLLBACK Committed --> Completed: async cleanup unstages into doc bodies Aborted --> RolledBack: async cleanup discards staged content Completed --> [*] RolledBack --> [*]

Durability. A transaction carries a durability level — MAJORITY (the default), MAJORITY_AND_PERSIST_TO_ACTIVE, or PERSIST_TO_MAJORITY — applied to both the staged writes and the commit, so an acknowledged commit survives node loss to the same degree as a durable KV write. See Concurrency, locking & durability.

Limits. A staged document must stay under 10 MB (below the 20 MB KV ceiling, because of staging overhead); cluster nodes must be NTP-synchronised for correct cleanup timing; and on a single-node cluster or a bucket with no replicas the durability level must be lowered to NONE, which weakens the crash guarantee. DDL is not transactional.

Contrast with a hand-rolled two-phase commit. Before native transactions, spanning two documents meant the application emulating a two-phase commit by hand: a separate "transaction" document holding the participant keys and a state field, per-participant pending markers written and then cleared, and a recovery job that scanned for stuck transaction documents to roll them forward or back. It worked, but it pushed atomicity, isolation and crash recovery into application code, and forced every reader to understand the pending markers. The native engine keeps the same moving parts — a transaction record, staged mutations, a recovery sweep — inside the server, behind transactions.run(…​). For the same feature elsewhere see SQL Transactions and MongoDB Transactions.