Access old values from RETURNING clause of UPDATE

Is it possible to access the value that a document hat before an UPDATE statement was executed from a RETURNING clause?

I’m for instance removing an element from an array using the ARRAY_REMOVE function in an UPDATE statement and would like to return whether an element was removed. I would solve this by returning whether the value was previously contained in the array, but how can I access the old value in the RETURNING clause? If I reference any fields in the RETURNING, then I always get the already updated value, not the previous value before the update.

So I’m looking for something like that:

UPDATE bucket b
USE KEYS “some-key”
SET b.arr = ARRAY_REMOVE(b.arr, “foo”)
RETURNING ARRAY_CONTAINS(OLD(b).arr, “foo”)

RETURNING clause is evaluated after document has mutated duet to that old value is not available it only has new values.

You can achieve with following and it is better approach because if the ARRAY doesn’t have the document never mutate (only mutate when value is present) save resources.

UPDATE bucket b
USE KEYS “some-key”
SET b.arr = ARRAY_REMOVE(b.arr, “foo”)
 WHERE  ANY v IN b.arr SATISFIES v = "foo" END
RETURNING true;
1 Like

Thanks a lot for your response this completely solved my issue! :grin:

A potential idea to extend the UPDATE statement would be to add another keyword allowing to compute variables based on the document value before the update was applied. Since the WHERE clause fetches the documents value before the update anyway, shouldn’t this almost be free to implement (so there aren’t any additional document fetches required)? So my example from above could look somewhat like that:

UPDATE bucket b
USE KEYS “some-key”
LET BEFORE was_included = ARRAY_CONTAINS(b.arr, “foo”)
SET b.arr = ARRAY_REMOVE(b.arr, “foo”)
RETURNING was_included

A year has passed meanwhile and there have been many cases where I’ve been missing this trivial feature.
I’ve always did not have any other option than building complex code using the key-value-api with CAS instead of writing an easy to read, declarative N1QL statement.

Is there any chance that we will see a feature that enables this functionality (for example the way I suggested or similar)? Especially since that feature should be free to implement since that information should be known to the Query-service while executing the UPDATE-query?

Track via MB-47277

1 Like

Thank you very much for creating the feature request, this would simplify so many things in the future :heart_eyes: