sql: support PG14 JSON and ARRAY subscripting for UPDATE operations

[steven-hubbard]

https://www.postgresql.org/docs/14/datatype-json.html#JSONB-SUBSCRIPTING

8.14.5. jsonb Subscripting
The jsonb data type supports array-style subscripting expressions to extract and modify elements. Nested values can be indicated by chaining subscripting expressions, following the same rules as the path argument in the jsonb_set function. If a jsonb value is an array, numeric subscripts start at zero, and negative integers count backwards from the last element of the array. Slice expressions are not supported. The result of a subscripting expression is always of the jsonb data type.

UPDATE statements may use subscripting in the SET clause to modify jsonb values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path val['a']['b']['c'] can be traversed all the way to c if every val, val['a'], and val['a']['b'] is an object. If any val['a'] or val['a']['b'] is not defined, it will be created as an empty object and filled as necessary. However, if any val itself or one of the intermediary values is defined as a non-object such as a string, number, or jsonb null, traversal cannot proceed so an error is raised and the transaction aborted.

Jira issue: CRDB-13589

Epic CRDB-12464

[mgartner]

cc @vy-ton for prioritization

[otan]

I've done SELECT support, but UPDATE is trickier and is deep into the weeds of the optimiser code.

In #83764, i've added support for single SET statements, e.g. UPDATE t SET k['a'] = '1', t[2] = '3'. This is done by replacing subscripts with a json_set clause. This in theory works for JSON, but should also work for other types that support this (e.g. hstore, which also supports subscripting). Happy to merge that if we're OK with partial support.

I imagine the following to be a small lift from that:

• UPDATE t SET (k['a'], j['b']) = (1, 2)

The bigger lifts will from supporting statements like these:

• UPDATE t SET k[0] = 1, k[1] = 2 -- note the same column is referenced multiple times. We currently do not allow this in the optimiser, and it'd be tricky-ish to shoehorn that in I'd imagine.
• UPDATE t SET (k[0], k[1]) = SELECT 1, 2 FROM tbl, where results are extracted from a subquery.

The future of supporting these kinds of statements is something that extends way outside my current knowledge. Probably needs a wordy discussion too. I'm no longer on SQL Experience with migrations work and have little time to follow up if this is a priority.

Maybe a good intern / starter project for someone starting new on the queries team? Unassigning myself for now...