aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/func.sgml23
-rw-r--r--src/backend/catalog/system_views.sql9
-rw-r--r--src/backend/utils/adt/jsonfuncs.c64
-rw-r--r--src/include/catalog/pg_proc.dat3
-rw-r--r--src/test/regress/expected/jsonb.out57
-rw-r--r--src/test/regress/sql/jsonb.sql20
6 files changed, 176 insertions, 0 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4b42f128625..72072e75459 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12232,6 +12232,9 @@ table2-mapping
<primary>jsonb_set</primary>
</indexterm>
<indexterm>
+ <primary>jsonb_set_lax</primary>
+ </indexterm>
+ <indexterm>
<primary>jsonb_insert</primary>
</indexterm>
<indexterm>
@@ -12546,6 +12549,26 @@ table2-mapping
</para></entry>
</row>
<row>
+ <entry><para><literal>jsonb_set_lax(target jsonb, path text[], new_value jsonb <optional>, create_missing boolean</optional> <optional>, null_value_treatment text</optional>)</literal>
+ </para></entry>
+ <entry><para><type>jsonb</type></para></entry>
+ <entry>
+ If <replaceable>new_value</replaceable> is not <literal>null</literal>,
+ behaves identically to <literal>jsonb_set</literal>. Otherwise behaves
+ according to the value of <replaceable>null_value_treatment</replaceable>
+ which must be one of <literal>'raise_exception'</literal>,
+ <literal>'use_json_null'</literal>, <literal>'delete_key'</literal>, or
+ <literal>'return_target'</literal>. The default is
+ <literal>'use_json_null'</literal>.
+ </entry>
+ <entry><para><literal>jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}',null)</literal>
+ </para><para><literal>jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}',null, true, 'return_target')</literal>
+ </para></entry>
+ <entry><para><literal>[{"f1":null,"f2":null},2,null,3]</literal>
+ </para><para><literal>[{"f1": 99, "f2": null}, 2]</literal>
+ </para></entry>
+ </row>
+ <row>
<entry>
<para><literal>
jsonb_insert(target jsonb, path text[], new_value jsonb <optional>, insert_after boolean</optional>)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b3e82de71e6..c9e75f43705 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1265,6 +1265,15 @@ STRICT IMMUTABLE PARALLEL SAFE
AS 'jsonb_set';
CREATE OR REPLACE FUNCTION
+ jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb,
+ create_if_missing boolean DEFAULT true,
+ null_value_treatment text DEFAULT 'use_json_null')
+RETURNS jsonb
+LANGUAGE INTERNAL
+CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE
+AS 'jsonb_set_lax';
+
+CREATE OR REPLACE FUNCTION
parse_ident(str text, strict boolean DEFAULT true)
RETURNS text[]
LANGUAGE INTERNAL
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index ab5a24a8584..4b5a0214dca 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -4396,6 +4396,70 @@ jsonb_set(PG_FUNCTION_ARGS)
/*
+ * SQL function jsonb_set_lax(jsonb, text[], jsonb, boolean, text)
+ */
+Datum
+jsonb_set_lax(PG_FUNCTION_ARGS)
+{
+ /* Jsonb *in = PG_GETARG_JSONB_P(0); */
+ /* ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); */
+ /* Jsonb *newval = PG_GETARG_JSONB_P(2); */
+ /* bool create = PG_GETARG_BOOL(3); */
+ text *handle_null;
+ char *handle_val;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(3))
+ PG_RETURN_NULL();
+
+ /* could happen if they pass in an explicit NULL */
+ if (PG_ARGISNULL(4))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("need delete_key, return_target, use_json_null, or raise_exception")));
+
+ /* if the new value isn't an SQL NULL just call jsonb_set */
+ if (! PG_ARGISNULL(2))
+ return jsonb_set(fcinfo);
+
+ handle_null = PG_GETARG_TEXT_P(4);
+ handle_val = text_to_cstring(handle_null);
+
+ if (strcmp(handle_val,"raise_exception") == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("NULL is not allowed"),
+ errdetail("exception raised due to \"null_value_treatment => 'raise_exception'\""),
+ errhint("to avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not used")));
+ }
+ else if (strcmp(handle_val, "use_json_null") == 0)
+ {
+ Datum newval;
+
+ newval = DirectFunctionCall1(jsonb_in, CStringGetDatum("null"));
+
+ fcinfo->args[2].value = newval;
+ fcinfo->args[2].isnull = false;
+ return jsonb_set(fcinfo);
+ }
+ else if (strcmp(handle_val, "delete_key") == 0)
+ {
+ return jsonb_delete_path(fcinfo);
+ }
+ else if (strcmp(handle_val, "return_target") == 0)
+ {
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ PG_RETURN_JSONB_P(in);
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("need delete_key, return_target, use_json_null, or raise_exception")));
+ }
+}
+
+/*
* SQL function jsonb_delete_path(jsonb, text[])
*/
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 427faa3c3b6..fcf2a1214c4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9305,6 +9305,9 @@
{ oid => '3304',
proname => 'jsonb_delete_path', prorettype => 'jsonb',
proargtypes => 'jsonb _text', prosrc => 'jsonb_delete_path' },
+{ oid => '8945', descr => 'Set part of a jsonb, handle NULL value',
+ proname => 'jsonb_set_lax', prorettype => 'jsonb', proisstrict => 'f',
+ proargtypes => 'jsonb _text jsonb bool text', prosrc => 'jsonb_set_lax' },
{ oid => '3305', descr => 'Set part of a jsonb',
proname => 'jsonb_set', prorettype => 'jsonb',
proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_set' },
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index a2a19f81041..b92f8e8dbc5 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4511,6 +4511,63 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
ERROR: path element at position 3 is not an integer: "non_integer"
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
ERROR: path element at position 3 is null
+-- jsonb_set_lax
+\pset null NULL
+-- pass though non nulls to jsonb_set
+select jsonb_set_lax('{"a":1,"b":2}','{b}','5') ;
+ jsonb_set_lax
+------------------
+ {"a": 1, "b": 5}
+(1 row)
+
+select jsonb_set_lax('{"a":1,"b":2}','{d}','6', true) ;
+ jsonb_set_lax
+--------------------------
+ {"a": 1, "b": 2, "d": 6}
+(1 row)
+
+-- using the default treatment
+select jsonb_set_lax('{"a":1,"b":2}','{b}',null);
+ jsonb_set_lax
+---------------------
+ {"a": 1, "b": null}
+(1 row)
+
+select jsonb_set_lax('{"a":1,"b":2}','{d}',null,true);
+ jsonb_set_lax
+-----------------------------
+ {"a": 1, "b": 2, "d": null}
+(1 row)
+
+-- errors
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, null);
+ERROR: need delete_key, return_target, use_json_null, or raise_exception
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, 'no_such_treatment');
+ERROR: need delete_key, return_target, use_json_null, or raise_exception
+-- explicit treatments
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'raise_exception') as raise_exception;
+ERROR: NULL is not allowed
+DETAIL: exception raised due to "null_value_treatment => 'raise_exception'"
+HINT: to avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not used
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'return_target') as return_target;
+ return_target
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key;
+ delete_key
+------------
+ {"a": 1}
+(1 row)
+
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null;
+ use_json_null
+---------------------
+ {"a": 1, "b": null}
+(1 row)
+
+\pset null
-- jsonb_insert
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
jsonb_insert
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index efd4c451853..3e2b8f66df2 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1153,6 +1153,26 @@ select jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"');
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"');
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
+-- jsonb_set_lax
+
+\pset null NULL
+
+-- pass though non nulls to jsonb_set
+select jsonb_set_lax('{"a":1,"b":2}','{b}','5') ;
+select jsonb_set_lax('{"a":1,"b":2}','{d}','6', true) ;
+-- using the default treatment
+select jsonb_set_lax('{"a":1,"b":2}','{b}',null);
+select jsonb_set_lax('{"a":1,"b":2}','{d}',null,true);
+-- errors
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, null);
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, 'no_such_treatment');
+-- explicit treatments
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'raise_exception') as raise_exception;
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'return_target') as return_target;
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key;
+select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null;
+
+\pset null
-- jsonb_insert
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 11:04:09 +0000