Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)

Hi,

Here is v4 of the JSON_TRANSFORM patch set. This revision closes the two
items that were still open in v3.

0005 is new. It adds the "= PATH <jsonpath>" source form for INSERT and
REPLACE, and enables the ON EMPTY and ON ERROR behaviour clauses that v3
parsed but rejected.

With a PATH source, the value to insert or replace is produced at run
time by evaluating the source jsonpath against the input document, the
same way JSON_QUERY without a wrapper does. The routing follows
SQL:2023 6.44 GR 5:

- a source path returning no items triggers ON EMPTY
(default NULL ON EMPTY);
- a source path returning more than one item, or raising a structural
error, triggers ON ERROR (default ERROR ON ERROR).

A few representative cases, checked against the standard's General Rules:

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH '$.a');
=> {"a": 10, "x": 10}

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH '$.nope');
=> {"a": 10, "x": null} (empty source, default NULL ON EMPTY)

SELECT JSON_TRANSFORM(jsonb '{"arr":[1,2,3]}', INSERT '$.x' = PATH
'$.arr[*]');
=> ERROR (more than one item, ERROR ON ERROR)

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH 'strict
$.nope');
=> ERROR (structural error, routed to ON ERROR)

Patches 0001 through 0004 are unchanged from v3 apart from the version
number.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
"there's no hogwarts without you hagrid"