Repository: genjidb/genji (go)

Issue:
Support dot notation on UPDATE ... SET
Currently, the `UPDATE` clause only accepts simple top-level fields `foo`.
We should be able to update nested fields or array indexes using [dot notation](https://genji.dev/docs/genji-sql/lexical-structure/#dot-notation).

```sql
UPDATE users SET a.3.name = [1, 2, 3];
```

Because nested values can be tricky, the SET clause must only set a value when it is possible. If we take the example above, the document must respect the following requirements in order to be settable:

- `a` is an array
- `a.3` exists and is a document
- `a.3.name` may or may not exist (same rules as for top-level fields, see below)

Currently, the rule is: _if the field doesn't exist, we create it, otherwise we replace it with the new value_.
With this change, the rule still applies but only for the element of the dot notation path that is the most to the right.

### Exception for arrays

```sql
INSERT INTO users (a) VALUES ([1, 0, 0]), ([2, 0]);
UPDATE users SET a.2 = 1;
SELECT * FROM users WHERE a.2 = 1;
{"a": [1, 0, 1]}
```

This example shows that the above rule must not apply for arrays. If the array index doesn't exist, it must not be created.

Pull request description:
Allow dot notation for UPDATE SET 
This PR fix #84 with the new format of `ValuePath` with change `UpdateSetPair`. It now like this.
```
type updateSetPair struct {
	path document.ValuePath
	e    expr.Expr
}
``` 
More tests was added. 


Patch:
diff --git a/document/array.go b/document/array.go
index 9fa5abdc..dd3b8a6f 100644
--- a/document/array.go
+++ b/document/array.go
@@ -7,7 +7,9 @@ import (
 
 // ErrValueNotFound must be returned by Array implementations, when calling the GetByIndex method and
 // the index wasn't found in the array.
-var ErrValueNotFound = errors.New("value not found")
+var (
+	ErrValueNotFound   = errors.New("value not found")
+)
 
 // An Array contains a set of values.
 type Array interface {
@@ -81,7 +83,7 @@ func (vb ValueBuffer) Iterate(fn func(i int, value Value) error) error {
 // GetByIndex returns a value set at the given index. If the index is out of range it returns an error.
 func (vb ValueBuffer) GetByIndex(i int) (Value, error) {
 	if i >= len(vb) {
-		return Value{}, ErrValueNotFound
+		return Value{}, ErrFieldNotFound
 	}
 
 	return vb[i], nil
diff --git a/document/document.go b/document/document.go
index e985d2ae..e6ed9b44 100644
--- a/document/document.go
+++ b/document/document.go
@@ -86,16 +86,94 @@ func (fb FieldBuffer) GetByField(field string) (Value, error) {
 	return Value{}, ErrFieldNotFound
 }
 
+// setFieldValue replaces a field if it already exists or creates one if not.
+func (fb *FieldBuffer) setFieldValue(field string, reqValue Value) error {
+	_, err := fb.GetByField(field)
+	switch err {
+	case ErrFieldNotFound:
+		fb.Add(field, reqValue)
+		return nil
+	case nil:
+		_ = fb.Replace(field, reqValue)
+		return nil
+	}
+
+	return err
+}
+
+// setValueAtPath deep replaces or creates a field
+// at the given path
+func setValueAtPath(v Value, p ValuePath, newValue Value) (Value, error) {
+	switch v.Type {
+	case DocumentValue:
+		var buf FieldBuffer
+		err := buf.ScanDocument(v.V.(Document))
+		if err != nil {
+			return v, err
+		}
+
+		if len(p) == 1 {
+			err = buf.setFieldValue(p[0].FieldName, newValue)
+			return NewDocumentValue(&buf), err
+		}
+
+		va, err := buf.GetByField(p[0].FieldName)
+		if err != nil {
+			return v, err
+		}
+
+		va, err = setValueAtPath(va, p[1:], newValue)
+		if err != nil {
+			return v, err
+		}
+
+		err = buf.setFieldValue(p[0].FieldName, va)
+		return NewDocumentValue(&buf), err
+	case ArrayValue:
+		var vb ValueBuffer
+		err := vb.ScanArray(v.V.(Array))
+		if err != nil {
+			return v, err
+		}
+
+		va, err := vb.GetByIndex(p[0].ArrayIndex)
+		if err != nil {
+			return v, err
+		}
+
+		if len(p) == 1 {
+			err = vb.Replace(p[0].ArrayIndex, newValue)
+			return NewArrayValue(&vb), err
+		}
+
+		va, err = setValueAtPath(va, p[1:], newValue)
+		err = vb.Replace(p[0].ArrayIndex, va)
+		return NewArrayValue(&vb), err
+	}
+
+	return v, nil
+}
+
 // Set replaces a field if it already exists or creates one if not.
-func (fb *FieldBuffer) Set(f string, v Value) {
+func (fb *FieldBuffer) Set(path ValuePath, v Value) error {
+	if len(path) == 1 {
+		return fb.setFieldValue(path[0].FieldName, v)
+	}
+
 	for i := range fb.fields {
-		if fb.fields[i].Field == f {
-			fb.fields[i].Value = v
-			return
+		if fb.fields[i].Field == path[0].FieldName {
+			va, err := setValueAtPath(fb.fields[i].Value, path[1:], v)
+			if err != nil {
+				return err
+			}
+
+			fb.fields[i].Value = va
+			return nil
 		}
 	}
 
-	fb.Add(f, v)
+	fb.Add(path[0].FieldName, v)
+	return nil
 }
 
 // Iterate goes through all the fields of the document and calls the given function by passing each one of them.
diff --git a/sql/parser/create.go b/sql/parser/create.go
index 67609040..792e1339 100644
--- a/sql/parser/create.go
+++ b/sql/parser/create.go
@@ -215,7 +215,7 @@ func (p *Parser) parseCreateIndexStatement(unique bool) (query.CreateIndexStmt,
 	}
 
 	if len(paths) != 1 {
-		return stmt, &ParseError{Message: "indexes on more than one field are not supported"}
+		return stmt, &ParseError{Message: "indexes on more than one path are not supported"}
 	}
 
 	stmt.Path = paths[0]
diff --git a/sql/parser/insert.go b/sql/parser/insert.go
index 4b1321ea..88b75f61 100644
--- a/sql/parser/insert.go
+++ b/sql/parser/insert.go
@@ -29,7 +29,7 @@ func (p *Parser) parseInsertStatement() (query.InsertStmt, error) {
 
 	valueParser := p.parseParamOrDocument
 
-	// Parse field list: (a, b, c)
+	// Parse path list: (a, b, c)
 	fields, withFields, err := p.parseFieldList()
 	if err != nil {
 		return stmt, err
@@ -45,7 +45,7 @@ func (p *Parser) parseInsertStatement() (query.InsertStmt, error) {
 		return stmt, err
 	}
 
-	// ensure the length of field list is the same as the length of values
+	// ensure the length of path list is the same as the length of values
 	if withFields {
 		for _, l := range values {
 			el := l.(expr.LiteralExprList)
@@ -59,7 +59,7 @@ func (p *Parser) parseInsertStatement() (query.InsertStmt, error) {
 	return stmt, nil
 }
 
-// parseFieldList parses a list of fields in the form: (field, field, ...), if exists
+// parseFieldList parses a list of fields in the form: (path, path, ...), if exists
 func (p *Parser) parseFieldList() ([]string, bool, error) {
 	// Parse ( token.
 	if tok, _, _ := p.ScanIgnoreWhitespace(); tok != scanner.LPAREN {
@@ -67,7 +67,7 @@ func (p *Parser) parseFieldList() ([]string, bool, error) {
 		return nil, false, nil
 	}
 
-	// Parse field list.
+	// Parse path list.
 	var fields []string
 	var err error
 	if fields, err = p.parseIdentList(); err != nil {
diff --git a/sql/parser/select.go b/sql/parser/select.go
index d7c050ed..26eb9cd4 100644
--- a/sql/parser/select.go
+++ b/sql/parser/select.go
@@ -13,7 +13,7 @@ func (p *Parser) parseSelectStatement() (*planner.Tree, error) {
 	var cfg selectConfig
 	var err error
 
-	// Parse field list or query.Wildcard
+	// Parse path list or query.Wildcard
 	cfg.ProjectionExprs, err = p.parseResultFields()
 	if err != nil {
 		return nil, err
@@ -58,7 +58,7 @@ func (p *Parser) parseSelectStatement() (*planner.Tree, error) {
 
 // parseResultFields parses the list of result fields.
 func (p *Parser) parseResultFields() ([]planner.ResultField, error) {
-	// Parse first (required) result field.
+	// Parse first (required) result path.
 	rf, err := p.parseResultField()
 	if err != nil {
 		return nil, err
@@ -93,7 +93,7 @@ func (p *Parser) parseResultField() (planner.ResultField, error) {
 		return nil, err
 	}
 
-	// FieldSelectors may be quoted, we make sure we name the result field
+	// FieldSelectors may be quoted, we make sure we name the result path
 	// with the unquoted name instead.
 	if fs, ok := e.(expr.FieldSelector); ok {
 		lit = fs.String()
@@ -129,7 +129,7 @@ func (p *Parser) parseFrom() (string, bool, error) {
 		return ident, true, pErr
 	}
 
-	return ident, true,  nil
+	return ident, true, nil
 }
 
 func (p *Parser) parseOrderBy() (expr.FieldSelector, scanner.Token, error) {
diff --git a/sql/parser/update.go b/sql/parser/update.go
index 2112176d..4befd7de 100644
--- a/sql/parser/update.go
+++ b/sql/parser/update.go
@@ -1,6 +1,7 @@
 package parser
 
 import (
+	"github.com/genjidb/genji/document"
 	"github.com/genjidb/genji/sql/planner"
 	"github.com/genjidb/genji/sql/query/expr"
 	"github.com/genjidb/genji/sql/scanner"
@@ -58,10 +59,12 @@ func (p *Parser) parseSetClause() ([]updateSetPair, error) {
 			}
 		}
 
-		// Scan the identifier for the field name.
-		tok, pos, lit := p.ScanIgnoreWhitespace()
-		if tok != scanner.IDENT {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"identifier"}, pos)
+		// Scan the identifier for the path name.
+		path, err := p.parsePath()
+		if err != nil {
+			pErr := err.(*ParseError)
+			pErr.Expected = []string{"path"}
+			return nil, pErr
 		}
 
 		// Scan the eq sign
@@ -74,7 +77,7 @@ func (p *Parser) parseSetClause() ([]updateSetPair, error) {
 		if err != nil {
 			return nil, err
 		}
-		pairs = append(pairs, updateSetPair{lit, expr})
+		pairs = append(pairs, updateSetPair{path, expr})
 
 		firstPair = false
 	}
@@ -96,7 +99,7 @@ func (p *Parser) parseUnsetClause() ([]string, error) {
 			}
 		}
 
-		// Scan the identifier for the field to unset.
+		// Scan the identifier for the path to unset.
 		tok, pos, lit := p.ScanIgnoreWhitespace()
 		if tok != scanner.IDENT {
 			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"identifier"}, pos)
@@ -113,20 +116,20 @@ type updateConfig struct {
 	TableName string
 
 	// SetPairs is used along with the Set clause. It holds
-	// each field with its corresponding value that
+	// each path with its corresponding value that
 	// should be set in the document.
 	SetPairs []updateSetPair
 
 	// UnsetFields is used along with the Unset clause. It holds
-	// each field that should be unset from the document.
+	// each path that should be unset from the document.
 	UnsetFields []string
 
 	WhereExpr expr.Expr
 }
 
 type updateSetPair struct {
-	field string
-	e     expr.Expr
+	path document.ValuePath
+	e    expr.Expr
 }
 
 // ToTree turns the statement into an expression tree.
@@ -139,7 +142,7 @@ func (cfg updateConfig) ToTree() *planner.Tree {
 
 	if cfg.SetPairs != nil {
 		for _, pair := range cfg.SetPairs {
-			t = planner.NewSetNode(t, pair.field, pair.e)
+			t = planner.NewSetNode(t, pair.path, pair.e)
 		}
 	} else if cfg.UnsetFields != nil {
 		for _, name := range cfg.UnsetFields {
diff --git a/sql/planner/optimizer.go b/sql/planner/optimizer.go
index a74c7ba7..c999c384 100644
--- a/sql/planner/optimizer.go
+++ b/sql/planner/optimizer.go
@@ -252,7 +252,7 @@ func RemoveUnnecessarySelectionNodesRule(t *Tree) (*Tree, error) {
 // UseIndexBasedOnSelectionNodeRule scans the tree for the first selection node whose condition is an
 // operator that satisfies the following criterias:
 // - implements the indexIteratorOperator interface
-// - one of its operands is a field selector that is indexed
+// - one of its operands is path selector that is indexed
 // - the other operand is a literal value or a parameter
 // If found, it will replace the input node by an indexInputNode using this index.
 func UseIndexBasedOnSelectionNodeRule(t *Tree) (*Tree, error) {
@@ -392,7 +392,7 @@ func selectionNodeValidForIndex(sn *selectionNode, tableName string, indexes map
 		return nil
 	}
 
-	// now, we look if an index exists for that field
+	// now, we look if an index exists for that path
 	idx, ok := indexes[field.Name()]
 	if !ok {
 		return nil
@@ -408,12 +408,12 @@ func opCanUseIndex(op expr.Operator) (bool, expr.FieldSelector, expr.Expr) {
 	lf, leftIsField := op.LeftHand().(expr.FieldSelector)
 	rf, rightIsField := op.RightHand().(expr.FieldSelector)
 
-	// field OP expr
+	// path OP expr
 	if leftIsField && !rightIsField {
 		return true, lf, op.RightHand()
 	}
 
-	// expr OP field
+	// expr OP path
 	if rightIsField && !leftIsField {
 		return true, rf, op.LeftHand()
 	}
diff --git a/sql/planner/sort.go b/sql/planner/sort.go
index 94bcddc7..48cb093b 100644
--- a/sql/planner/sort.go
+++ b/sql/planner/sort.go
@@ -22,7 +22,7 @@ type sortNode struct {
 var _ operationNode = (*sortNode)(nil)
 
 // NewSortNode creates a node that sorts a stream according to a given
-// document field and a sort direction.
+// document path and a sort direction.
 func NewSortNode(n Node, sortField expr.FieldSelector, direction scanner.Token) Node {
 	if direction == 0 {
 		direction = scanner.ASC
diff --git a/sql/planner/tree.go b/sql/planner/tree.go
index 57dd0ac8..cec8158a 100644
--- a/sql/planner/tree.go
+++ b/sql/planner/tree.go
@@ -24,7 +24,7 @@ const (
 	Selection
 	// Projection (∏) is an operation that selects a list of fields from each document of a stream.
 	Projection
-	// Rename (ρ) is an operation that renames a field from each document of a stream.
+	// Rename (ρ) is an operation that renames a path from each document of a stream.
 	Rename
 	// Deletion is an operation that removes all of the documents of a stream from their respective table.
 	Deletion
@@ -35,11 +35,11 @@ const (
 	Limit
 	// Skip is an operation that ignores a certain number of documents.
 	Skip
-	// Sort is an operation that sorts a stream of document according to a given field and a direction.
+	// Sort is an operation that sorts a stream of document according to a given path and a direction.
 	Sort
-	// Set is an operation that adds or replaces a field for every document of the stream.
+	// Set is an operation that adds or replaces a path for every document of the stream.
 	Set
-	// Unset is an operation that removes a field from every document of a stream
+	// Unset is an operation that removes a path from every document of a stream
 	Unset
 )
 
@@ -317,8 +317,8 @@ func (n *offsetNode) toStream(st document.Stream) (document.Stream, error) {
 type setNode struct {
 	node
 
-	field string
-	e     expr.Expr
+	path document.ValuePath
+	e    expr.Expr
 
 	tx     *database.Transaction
 	params []expr.Param
@@ -326,15 +326,15 @@ type setNode struct {
 
 var _ operationNode = (*setNode)(nil)
 
-// NewSetNode creates a node that adds or replaces a field for every document of the stream.
-func NewSetNode(n Node, field string, e expr.Expr) Node {
+// NewSetNode creates a node that adds or replaces a path for every document of the stream.
+func NewSetNode(n Node, path document.ValuePath, e expr.Expr) Node {
 	return &setNode{
 		node: node{
 			op:   Set,
 			left: n,
 		},
-		field: field,
-		e:     e,
+		path: path,
+		e:    e,
 	}
 }
 
@@ -345,7 +345,7 @@ func (n *setNode) Bind(tx *database.Transaction, params []expr.Param) (err error
 }
 
 func (n *setNode) String() string {
-	return fmt.Sprintf("Set(%s = %s)", n.field, n.e)
+	return fmt.Sprintf("Set(%s = %s)", n.path, n.e)
 }
 
 func (n *setNode) toStream(st document.Stream) (document.Stream, error) {
@@ -370,17 +370,9 @@ func (n *setNode) toStream(st document.Stream) (document.Stream, error) {
 			return nil, err
 		}
 
-		_, err = fb.GetByField(n.field)
-
-		switch err {
-		case nil:
-			// If no error, it means that the field already exists
-			// and it should be replaced.
-			_ = fb.Replace(n.field, ev)
-		case document.ErrFieldNotFound:
-			// If the field doesn't exist,
-			// it should be added to the document.
-			fb.Set(n.field, ev)
+		err = fb.Set(n.path, ev)
+		if err != nil {
+			return nil, err
 		}
 
 		return &fb, nil
@@ -395,7 +387,7 @@ type unsetNode struct {
 
 var _ operationNode = (*unsetNode)(nil)
 
-// NewUnsetNode creates a node that adds or replaces a field for every document of the stream.
+// NewUnsetNode creates a node that adds or replaces a path for every document of the stream.
 func NewUnsetNode(n Node, field string) Node {
 	return &unsetNode{
 		node: node{


Test patch:
diff --git a/document/document_test.go b/document/document_test.go
index 4bd49ad6..675f052d 100644
--- a/document/document_test.go
+++ b/document/document_test.go
@@ -1,6 +1,7 @@
 package document_test
 
 import (
+	"bytes"
 	"encoding/json"
 	"errors"
 	"testing"
@@ -78,20 +79,62 @@ func TestFieldBuffer(t *testing.T) {
 	})
 
 	t.Run("Set", func(t *testing.T) {
-		var buf document.FieldBuffer
-		buf.Add("a", document.NewIntegerValue(10))
-		buf.Add("b", document.NewTextValue("hello"))
 
-		buf.Set("a", document.NewDoubleValue(11))
-		v, err := buf.GetByField("a")
-		require.NoError(t, err)
-		require.Equal(t, document.NewDoubleValue(11), v)
+		tests := []struct {
+			name  string
+			data  string
+			path  string
+			value document.Value
+			want  string
+			fails bool
+		}{
+			{"root", `{}`, `a`, document.NewIntegerValue(1), `{"a": 1}`, false},
+			{"add field", `{"a": {"b": [1, 2, 3]}}`, `c`, document.NewTextValue("foo"), `{"a": {"b": [1, 2, 3]}, "c": "foo"}`, false},
+			{"nested doc", `{"a": "foo"}`, `a`, document.NewDocumentValue(document.NewFieldBuffer().
+				Add("b", document.NewArrayValue(document.NewValueBuffer().
+				Append(document.NewIntegerValue(1)).
+				Append(document.NewIntegerValue(2)).
+				Append(document.NewIntegerValue(3))))), `{"a": {"b": [1, 2, 3]}}`, false},
+			{"nested doc", `{"a": {"b": [1, 2, 3]}}`, `a.b`, document.NewArrayValue(document.NewValueBuffer().
+				Append(document.NewIntegerValue(1)).
+				Append(document.NewIntegerValue(2)).
+				Append(document.NewIntegerValue(3))), `{"a": {"b": [1, 2, 3]}}`, false},
+			{"nested array", `{"a": {"b": [1, 2, 3]}}`, `a.b[1]`, document.NewIntegerValue(1), `{"a": {"b": [1, 1, 3]}}`, false},
+			{"nested array multiple indexes", `{"a": {"b": [1, 2, [1, 2, {"c": "foo"}]]}}`, `a.b[2][2].c`, document.NewTextValue("bar"), `{"a": {"b": [1, 2, [1, 2, {"c": "bar"}]]}}`, false},
+			{"number field", `{"a": {"0": [1, 2, 3]}}`, "a.`0`[0]", document.NewIntegerValue(6), `{"a": {"0": [6, 2, 3]}}`, false},
+			{"document in array", `{"a": [{"b":"foo"}, 2, 3]}`, `a[0].b`, document.NewTextValue("bar"), `{"a": [{"b": "bar"}, 2, 3]}`, false},
+			// with errors or request ignored doc unchanged
+			{"field not found", `{"a": {"b": [1, 2, 3]}}`, `a.b.c`, document.NewIntegerValue(1), `{"a": {"b": [1, 2, 3]}}`, false},
+			{"unknown path", `{"a": {"b": [1, 2, 3]}}`, `a.e.f`, document.NewIntegerValue(1), ``, true},
+			{"index out of range", `{"a": {"b": [1, 2, 3]}}`, `a.b[1000]`, document.NewIntegerValue(1), ``, true},
+			{"document not array", `{"a": {"b": "foo"}}`, `a[0].b`, document.NewTextValue("bar"), ``, true},
 
-		buf.Set("c", document.NewIntegerValue(12))
-		require.Equal(t, 3, buf.Len())
-		v, err = buf.GetByField("c")
-		require.NoError(t, err)
-		require.Equal(t, document.NewIntegerValue(12), v)
+		}
+
+		for _, tt := range tests {
+			t.Run(tt.name, func(t *testing.T) {
+				var fb document.FieldBuffer
+
+				d, err := document.NewFromJSON([]byte(tt.data))
+				require.NoError(t, err)
+				err = fb.Copy(d)
+				require.NoError(t, err)
+				p, err := parser.ParsePath(tt.path)
+				require.NoError(t, err)
+				err = fb.Set(p, tt.value)
+				if tt.fails {
+					require.Error(t, err)
+					return
+				}
+
+				require.NoError(t, err)
+				var bufBytes bytes.Buffer
+				require.NoError(t, err)
+				err = document.ToJSON(&bufBytes, fb)
+				require.NoError(t, err)
+				require.Equal(t, tt.want, bufBytes.String())
+			})
+		}
 	})
 
 	t.Run("Delete", func(t *testing.T) {
diff --git a/sql/parser/create_test.go b/sql/parser/create_test.go
index 31635a62..6d02b2c7 100644
--- a/sql/parser/create_test.go
+++ b/sql/parser/create_test.go
@@ -143,7 +143,7 @@ func TestParserCreateIndex(t *testing.T) {
 		{"If not exists", "CREATE INDEX IF NOT EXISTS idx ON test (foo.bar[1])", query.CreateIndexStmt{IndexName: "idx", TableName: "test", Path: parsePath(t, "foo.bar[1]"), IfNotExists: true}, false},
 		{"Unique", "CREATE UNIQUE INDEX IF NOT EXISTS idx ON test (foo[3].baz)", query.CreateIndexStmt{IndexName: "idx", TableName: "test", Path: parsePath(t, "foo[3].baz"), IfNotExists: true, Unique: true}, false},
 		{"No fields", "CREATE INDEX idx ON test", nil, true},
-		{"More than 1 field", "CREATE INDEX idx ON test (foo, bar)", nil, true},
+		{"More than 1 path", "CREATE INDEX idx ON test (foo, bar)", nil, true},
 	}
 
 	for _, test := range tests {
diff --git a/sql/parser/select_test.go b/sql/parser/select_test.go
index 467e9628..4c56fc14 100644
--- a/sql/parser/select_test.go
+++ b/sql/parser/select_test.go
@@ -39,11 +39,11 @@ func TestParserSelect(t *testing.T) {
 					"test",
 				)),
 			false},
-		{"WithFieldsWithQuotes", "SELECT `long \"field\"` FROM test",
+		{"WithFieldsWithQuotes", "SELECT `long \"path\"` FROM test",
 			planner.NewTree(
 				planner.NewProjectionNode(
 					planner.NewTableInputNode("test"),
-					[]planner.ResultField{planner.ResultFieldExpr{Expr: expr.FieldSelector(parsePath(t, "`long \"field\"`")), ExprName: "long \"field\""}},
+					[]planner.ResultField{planner.ResultFieldExpr{Expr: expr.FieldSelector(parsePath(t, "`long \"path\"`")), ExprName: "long \"path\""}},
 					"test",
 				)),
 			false},
diff --git a/sql/parser/update_test.go b/sql/parser/update_test.go
index ef936852..61a2143b 100644
--- a/sql/parser/update_test.go
+++ b/sql/parser/update_test.go
@@ -20,7 +20,7 @@ func TestParserUpdate(t *testing.T) {
 				planner.NewReplacementNode(
 					planner.NewSetNode(
 						planner.NewTableInputNode("test"),
-						"a", expr.IntegerValue(1),
+						parsePath(t, "a"), expr.IntegerValue(1),
 					),
 					"test",
 				)),
@@ -34,9 +34,59 @@ func TestParserUpdate(t *testing.T) {
 								planner.NewTableInputNode("test"),
 								expr.Eq(expr.FieldSelector(parsePath(t, "age")), expr.IntegerValue(10)),
 							),
-							"a", expr.IntegerValue(1),
+							parsePath(t, "a"), expr.IntegerValue(1),
 						),
-						"b", expr.IntegerValue(2),
+						parsePath(t, "b"), expr.IntegerValue(2),
+					),
+					"test",
+				)),
+			false},
+		{"SET/No cond path with backquotes", "UPDATE test SET `   some \"path\" ` = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						parsePath(t, "`   some \"path\" `"), expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
+		{"SET/No cond nested path", "UPDATE test SET a.b = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						parsePath(t, "a.b"), expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
+		{"SET/No cond nested path with backquotes", "UPDATE test SET a.` b `.c = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						parsePath(t, "a.` b `.c"), expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
+		{"SET/No cond array index", "UPDATE test SET a[1] = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						parsePath(t, "a[1]"), expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
+		{"SET/No cond nested array index", "UPDATE test SET a.b[100][10].c = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						parsePath(t, "a.b[100][10].c"), expr.IntegerValue(1),
 					),
 					"test",
 				)),
@@ -81,6 +131,7 @@ func TestParserUpdate(t *testing.T) {
 				require.Error(t, err)
 				return
 			}
+
 			require.NoError(t, err)
 			require.Len(t, q.Statements, 1)
 			require.EqualValues(t, test.expected, q.Statements[0])
diff --git a/sql/planner/optimizer_test.go b/sql/planner/optimizer_test.go
index bb1636cf..465ac627 100644
--- a/sql/planner/optimizer_test.go
+++ b/sql/planner/optimizer_test.go
@@ -211,7 +211,7 @@ func TestUseIndexBasedOnSelectionNodeRule(t *testing.T) {
 		root, expected planner.Node
 	}{
 		{
-			"non-indexed field",
+			"non-indexed path",
 			planner.NewSelectionNode(planner.NewTableInputNode("foo"),
 				expr.Eq(
 					expr.FieldSelector{document.ValuePathFragment{FieldName: "d"}},
diff --git a/sql/query/update_test.go b/sql/query/update_test.go
index d7ff9aef..52590448 100644
--- a/sql/query/update_test.go
+++ b/sql/query/update_test.go
@@ -77,22 +77,50 @@ func TestUpdateStmt(t *testing.T) {
 	}
 
 	t.Run("with arrays", func(t *testing.T) {
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		res, err := db.Query(`
-			CREATE TABLE foo;
-			INSERT INTO foo (a) VALUES ([1, 0, 0]), ([2, 0]);
-			UPDATE foo set b = 0;
-			SELECT * FROM foo; 
-		`)
-		require.NoError(t, err)
-		defer res.Close()
-
-		var buf bytes.Buffer
-		err = document.IteratorToJSONArray(&buf, res)
-		require.NoError(t, err)
-		require.JSONEq(t, `[{"a": [1, 0, 0], "b": 0}, {"a": [2, 0], "b": 0}]`, buf.String())
+		tests := []struct {
+			name     string
+			query    string
+			fails    bool
+			expected string
+			params   []interface{}
+		}{
+			{"SET / No cond add field ", `UPDATE foo set b = 0`, false, `[{"a": [1, 0, 0], "b": 0}, {"a": [2, 0], "b": 0}]`, nil},
+			{"SET / No cond / with path at existing index only", `UPDATE foo SET a[2] = 10`, false, `[{"a": [1, 0, 10]}, {"a": [2, 0]}]`, nil},
+			{"SET / No cond / with index array", `UPDATE foo SET a[1] = 10`, false, `[{"a": [1, 10, 0]}, {"a": [2, 10]}]`, nil},
+			{"SET / No cond / with path on non existing field", `UPDATE foo SET a.foo[1] = 10`, false, `[{"a": [1, 0, 0]}, {"a": [2, 0]}]`, nil},
+			{"SET / With cond / index array", `UPDATE foo SET a[0] = 1 WHERE a[0] = 2`, false, `[{"a": [1, 0, 0]}, {"a": [1, 0]}]`, nil},
+			{"SET / No cond / index out of range", `UPDATE foo SET a[10] = 1`, true, `[{"a": [1, 0, 0]}, {"a": [1, 0]}]`, nil},
+			{"SET / No cond / Nested array", `UPDATE foo SET a[1] = [1, 0, 0]`, false, `[{"a": [1, [1, 0, 0], 0]}, {"a": [2, [1, 0, 0]]}]`, nil},
+			{"SET / No cond / with multiple idents", `UPDATE foo SET a[1] = [1, 0, 0], a[1][2] = 9`, false, `[{"a": [1, [1, 0, 9], 0]}, {"a": [2, [1, 0, 9]]}]`, nil},
+			{"SET / No cond / add doc / with multiple idents with multiple indexes", `UPDATE foo SET a[1] = [1, 0, 0], a[1][2] = {"b": "foo"}`, false, `[{"a": [1, [1, 0, {"b":"foo"}], 0]}, {"a": [2, [1, 0, {"b":"foo"}]]}]`, nil},
+		}
+
+		for _, tt := range tests {
+			db, err := genji.Open(":memory:")
+			require.NoError(t, err)
+			defer db.Close()
+
+			err = db.Exec(`CREATE TABLE foo;`)
+			require.NoError(t, err)
+			err = db.Exec(`INSERT INTO foo (a) VALUES ([1, 0, 0]), ([2, 0]);`)
+			require.NoError(t, err)
+
+			err = db.Exec(tt.query, tt.params...)
+			if tt.fails {
+				require.Error(t, err)
+				continue
+			}
+
+			require.NoError(t, err)
+			st, err := db.Query("SELECT * FROM foo")
+			require.NoError(t, err)
+			defer st.Close()
+
+			var buf bytes.Buffer
+
+			err = document.IteratorToJSONArray(&buf, st)
+			require.NoError(t, err)
+			require.JSONEq(t, tt.expected, buf.String())
+		}
 	})
 }