[CALCITE-2884] Implement JSON_INSERT, JSON_REPLACE, JSON_SET

[XuQianJin-Stars]

JSON_INSERT(jsondoc, path, val[, path, val] )

JSON_REPLACE(jsondoc, path, val[, path, val] )

JSON_SET(jsondoc, path, _val[, path, val] )

Inserts data into a JSON document and returns the result. Returns {{NULL}} if any argument is {{NULL}}. An error occurs if the {{json_doc}} argument is not a valid JSON document or any {{path}} argument is not a valid path expression or contains a or {{}}}}{{{{*}} wildcard.

The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

• A member not present in an existing object. The member is added to the object and associated with the new value.

• A position past the end of an existing array. The array is extended with the new value. If the existing value is not an array, it is autowrapped as an array, then extended with the new value.

Otherwise, a path-value pair for a nonexisting path in the document is ignored and has no effect.

For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

JSON_INSERT SQL:

SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
 FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

Result:

c1
{"a": 1, "b": [2, 3], "c": "[true, false]"}

JSON_REPLACE SQL:

SELECT JSON_REPLACE(v, '$.a', 10, '$.c', '[true, false]') AS c1
 FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

Result:

c1
{"a": 10, "b": [2, 3],}

JSON_SET SQL:

SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
 FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

Result:

c1
{"a": 10, "b": [2, 3], "c": "[true, false]"}

[XuQianJin-Stars]

hi @zhztheplayer @michaelmior I don't know if all the considerations are incomplete. I hope you can review and give me some Suggestions. Thank you very much. best qianjin

[XuQianJin-Stars]

hi @michaelmior @zhztheplayer Thank you very much. Can you review this PR? best qianjin

[zhztheplayer]

hi @michaelmior @zhztheplayer Thank you very much. Can you review this PR? best qianjin

I'll help review when I have enough time. Sorry for letting you wait.

[XuQianJin-Stars]

hi @zhztheplayer Thank you very much，Comments addressed.

[XuQianJin-Stars]

hi @zhztheplayer Thank you very much, Comments addressed.

[XuQianJin-Stars]

hi @danny0405 Thank you very much, Comments addressed.

[oleg-smith]

@zabetak @julianhyde is there a chance to merge this PR? Looks like all comments were addressed.

[julianhyde]

@oleg-smith The PR looks reasonable but is very old and so needs to rebased. If someone rebases I will merge.

Please move the conversation to https://issues.apache.org/jira/browse/CALCITE-2884. I don't receive most GitHub notifications.

[oleg-smith]

@XuQianJin-Stars any chance to rebase it, as it's ready to be merged? I'd really appreciate it!

[zabetak]

@oleg-smith I am rebasing now and will try to get this by the end of today.

[zabetak]

Closing in favor of https://github.com/apache/calcite/pull/3020. I didn't push the changes here cause I didn't want to rebase a branch that I do not own.