Partial Update Syntax [Deprecated]

FB DocStore is deprecated, and users will need to find alternative solutions.

Traditionally, to update a column, you will need to provide the new value for the column. Since JSON could be large in a document column, we introduce a new update syntax to allow you provide only the part of a JSON that needs to be updated. We call it partial update syntax.

Syntax

We have two ways to update a document column: using document path directly, or using functions.

Partial update using document path

UPDATE TABLE_NAME
SET COL_NAME.document.path = new_value|expression [INSERT] [IF [NOT] EXIST]
WHERE ...

Notes:

• INSERT means that the value will be inserted into the array,
• IF [NOT] EXIST means that only when the document path exist or not exist, the update will be executed.

Partial update using functions

A more powerful syntax is to use functions.

UPDATE TABLE_NAME
SET COL_NAME.document.path.FUNC(...)
WHERE ...

FUNC(...) can be one of the following functions that are supported in DocStore. Note the functions here only can be used in document column partial update.

• set(new_value | expression): assign the new_value to a given path.
• setExists(new_value | expression): assign the new value to a given path when the document path exists.
• setNotExists(new_value | expression): assign the new value to a given path when the document path does not exist.
• unset(): delete the element in a given path.
• inc(value | expression): increment the element of a given path by value.
• insertAt(index, value | expression): insert value before the index.
• insertAtExists(index, value | expression): insert value before the index, when the index exists.
• insertAtNotExists(index, value | expression): insert value before the index, when the index doesn't exists.

Examples

-- update objects
update t1 
set t1.doc.address.houseNumber = 2580, 
    t1.doc.address.streetName = "El Camino Real",
    t1.doc.address.city = "Redwood City"
where  doc.name = 'Alex';

-- update objects using functions
update t2 
set t2.doc.address.houseNumber.set(1234),
    t2.doc.address.streetName.set("Willow"),
    t2.doc.address.city.set("Menlo Park")
where id = 2;

-- non-existence index will not update the array
update t1 set t1.doc.children.5 = "Jenny" where id = 2;

-- insert before index
update t1 set t1.doc.children.1 = "Penny" INSERT where id = 2;
update t1 set t1.doc.children.insertAt(1, "Jenny") where id = 2;

-- update with document path itself
update t1 set t1.doc.age = 50 where doc.name = 'Alex';
update t1 set t1.doc.age = t1.doc.age + 1 IF EXISTS;
update t1 set t1.doc.age.inc(1);

-- delete an element
update t1 set t1.doc.age.unset() where  doc.name = 'Alex';