Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita83586b

Browse files
committed
Add a non-strict version of jsonb_set
jsonb_set_lax() is the same as jsonb_set, except that it takes and extraargument that specifies what to do if the value argument is NULL. Thedefault is 'use_json_null'. Other possibilities are 'raise_exception','return_target' and 'delete_key', all these behaviours having beensuggested as reasonable by various users.Discussion:https://postgr.es/m/375873e2-c957-3a8d-64f9-26c43c2b16e7@2ndQuadrant.comReviewed by: Pavel Stehule
1 parentf7cd589 commita83586b

File tree

6 files changed

+176
-0
lines changed

6 files changed

+176
-0
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12231,6 +12231,9 @@ table2-mapping
1223112231
<indexterm>
1223212232
<primary>jsonb_set</primary>
1223312233
</indexterm>
12234+
<indexterm>
12235+
<primary>jsonb_set_lax</primary>
12236+
</indexterm>
1223412237
<indexterm>
1223512238
<primary>jsonb_insert</primary>
1223612239
</indexterm>
@@ -12545,6 +12548,26 @@ table2-mapping
1254512548
</para><para><literal>[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</literal>
1254612549
</para></entry>
1254712550
</row>
12551+
<row>
12552+
<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>
12553+
</para></entry>
12554+
<entry><para><type>jsonb</type></para></entry>
12555+
<entry>
12556+
If <replaceable>new_value</replaceable> is not <literal>null</literal>,
12557+
behaves identically to <literal>jsonb_set</literal>. Otherwise behaves
12558+
according to the value of <replaceable>null_value_treatment</replaceable>
12559+
which must be one of <literal>'raise_exception'</literal>,
12560+
<literal>'use_json_null'</literal>, <literal>'delete_key'</literal>, or
12561+
<literal>'return_target'</literal>. The default is
12562+
<literal>'use_json_null'</literal>.
12563+
</entry>
12564+
<entry><para><literal>jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}',null)</literal>
12565+
</para><para><literal>jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}',null, true, 'return_target')</literal>
12566+
</para></entry>
12567+
<entry><para><literal>[{"f1":null,"f2":null},2,null,3]</literal>
12568+
</para><para><literal>[{"f1": 99, "f2": null}, 2]</literal>
12569+
</para></entry>
12570+
</row>
1254812571
<row>
1254912572
<entry>
1255012573
<para><literal>

‎src/backend/catalog/system_views.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1264,6 +1264,15 @@ LANGUAGE INTERNAL
12641264
STRICT IMMUTABLE PARALLEL SAFE
12651265
AS'jsonb_set';
12661266

1267+
CREATEOR REPLACE FUNCTION
1268+
jsonb_set_lax(jsonb_in jsonb,pathtext[] , replacement jsonb,
1269+
create_if_missingboolean DEFAULT true,
1270+
null_value_treatmenttext DEFAULT'use_json_null')
1271+
RETURNS jsonb
1272+
LANGUAGE INTERNAL
1273+
CALLEDONNULL INPUT IMMUTABLE PARALLEL SAFE
1274+
AS'jsonb_set_lax';
1275+
12671276
CREATEOR REPLACE FUNCTION
12681277
parse_ident(strtext, strictboolean DEFAULT true)
12691278
RETURNStext[]

‎src/backend/utils/adt/jsonfuncs.c

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4395,6 +4395,70 @@ jsonb_set(PG_FUNCTION_ARGS)
43954395
}
43964396

43974397

4398+
/*
4399+
* SQL function jsonb_set_lax(jsonb, text[], jsonb, boolean, text)
4400+
*/
4401+
Datum
4402+
jsonb_set_lax(PG_FUNCTION_ARGS)
4403+
{
4404+
/* Jsonb *in = PG_GETARG_JSONB_P(0); */
4405+
/* ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); */
4406+
/* Jsonb *newval = PG_GETARG_JSONB_P(2); */
4407+
/* boolcreate = PG_GETARG_BOOL(3); */
4408+
text*handle_null;
4409+
char*handle_val;
4410+
4411+
if (PG_ARGISNULL(0)||PG_ARGISNULL(1)||PG_ARGISNULL(3))
4412+
PG_RETURN_NULL();
4413+
4414+
/* could happen if they pass in an explicit NULL */
4415+
if (PG_ARGISNULL(4))
4416+
ereport(ERROR,
4417+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4418+
errmsg("need delete_key, return_target, use_json_null, or raise_exception")));
4419+
4420+
/* if the new value isn't an SQL NULL just call jsonb_set */
4421+
if (!PG_ARGISNULL(2))
4422+
returnjsonb_set(fcinfo);
4423+
4424+
handle_null=PG_GETARG_TEXT_P(4);
4425+
handle_val=text_to_cstring(handle_null);
4426+
4427+
if (strcmp(handle_val,"raise_exception")==0)
4428+
{
4429+
ereport(ERROR,
4430+
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
4431+
errmsg("NULL is not allowed"),
4432+
errdetail("exception raised due to \"null_value_treatment => 'raise_exception'\""),
4433+
errhint("to avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not used")));
4434+
}
4435+
elseif (strcmp(handle_val,"use_json_null")==0)
4436+
{
4437+
Datumnewval;
4438+
4439+
newval=DirectFunctionCall1(jsonb_in,CStringGetDatum("null"));
4440+
4441+
fcinfo->args[2].value=newval;
4442+
fcinfo->args[2].isnull= false;
4443+
returnjsonb_set(fcinfo);
4444+
}
4445+
elseif (strcmp(handle_val,"delete_key")==0)
4446+
{
4447+
returnjsonb_delete_path(fcinfo);
4448+
}
4449+
elseif (strcmp(handle_val,"return_target")==0)
4450+
{
4451+
Jsonb*in=PG_GETARG_JSONB_P(0);
4452+
PG_RETURN_JSONB_P(in);
4453+
}
4454+
else
4455+
{
4456+
ereport(ERROR,
4457+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4458+
errmsg("need delete_key, return_target, use_json_null, or raise_exception")));
4459+
}
4460+
}
4461+
43984462
/*
43994463
* SQL function jsonb_delete_path(jsonb, text[])
44004464
*/

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9305,6 +9305,9 @@
93059305
{ oid => '3304',
93069306
proname => 'jsonb_delete_path', prorettype => 'jsonb',
93079307
proargtypes => 'jsonb _text', prosrc => 'jsonb_delete_path' },
9308+
{ oid => '8945', descr => 'Set part of a jsonb, handle NULL value',
9309+
proname => 'jsonb_set_lax', prorettype => 'jsonb', proisstrict => 'f',
9310+
proargtypes => 'jsonb _text jsonb bool text', prosrc => 'jsonb_set_lax' },
93089311
{ oid => '3305', descr => 'Set part of a jsonb',
93099312
proname => 'jsonb_set', prorettype => 'jsonb',
93109313
proargtypes => 'jsonb _text jsonb bool', prosrc => 'jsonb_set' },

‎src/test/regress/expected/jsonb.out

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4511,6 +4511,63 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
45114511
ERROR: path element at position 3 is not an integer: "non_integer"
45124512
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
45134513
ERROR: path element at position 3 is null
4514+
-- jsonb_set_lax
4515+
\pset null NULL
4516+
-- pass though non nulls to jsonb_set
4517+
select jsonb_set_lax('{"a":1,"b":2}','{b}','5') ;
4518+
jsonb_set_lax
4519+
------------------
4520+
{"a": 1, "b": 5}
4521+
(1 row)
4522+
4523+
select jsonb_set_lax('{"a":1,"b":2}','{d}','6', true) ;
4524+
jsonb_set_lax
4525+
--------------------------
4526+
{"a": 1, "b": 2, "d": 6}
4527+
(1 row)
4528+
4529+
-- using the default treatment
4530+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null);
4531+
jsonb_set_lax
4532+
---------------------
4533+
{"a": 1, "b": null}
4534+
(1 row)
4535+
4536+
select jsonb_set_lax('{"a":1,"b":2}','{d}',null,true);
4537+
jsonb_set_lax
4538+
-----------------------------
4539+
{"a": 1, "b": 2, "d": null}
4540+
(1 row)
4541+
4542+
-- errors
4543+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, null);
4544+
ERROR: need delete_key, return_target, use_json_null, or raise_exception
4545+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, 'no_such_treatment');
4546+
ERROR: need delete_key, return_target, use_json_null, or raise_exception
4547+
-- explicit treatments
4548+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'raise_exception') as raise_exception;
4549+
ERROR: NULL is not allowed
4550+
DETAIL: exception raised due to "null_value_treatment => 'raise_exception'"
4551+
HINT: to avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not used
4552+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'return_target') as return_target;
4553+
return_target
4554+
------------------
4555+
{"a": 1, "b": 2}
4556+
(1 row)
4557+
4558+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key;
4559+
delete_key
4560+
------------
4561+
{"a": 1}
4562+
(1 row)
4563+
4564+
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null;
4565+
use_json_null
4566+
---------------------
4567+
{"a": 1, "b": null}
4568+
(1 row)
4569+
4570+
\pset null
45144571
-- jsonb_insert
45154572
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
45164573
jsonb_insert

‎src/test/regress/sql/jsonb.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1153,6 +1153,26 @@ select jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"');
11531153
select jsonb_set('{"a": {"b": [1, 2, 3]}}','{a, b, non_integer}','"new_value"');
11541154
select jsonb_set('{"a": {"b": [1, 2, 3]}}','{a, b, NULL}','"new_value"');
11551155

1156+
-- jsonb_set_lax
1157+
1158+
\psetnullNULL
1159+
1160+
-- pass though non nulls to jsonb_set
1161+
select jsonb_set_lax('{"a":1,"b":2}','{b}','5') ;
1162+
select jsonb_set_lax('{"a":1,"b":2}','{d}','6', true) ;
1163+
-- using the default treatment
1164+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null);
1165+
select jsonb_set_lax('{"a":1,"b":2}','{d}',null,true);
1166+
-- errors
1167+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, true,null);
1168+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, true,'no_such_treatment');
1169+
-- explicit treatments
1170+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, null_value_treatment=>'raise_exception')as raise_exception;
1171+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, null_value_treatment=>'return_target')as return_target;
1172+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, null_value_treatment=>'delete_key')as delete_key;
1173+
select jsonb_set_lax('{"a":1,"b":2}','{b}',null, null_value_treatment=>'use_json_null')as use_json_null;
1174+
1175+
\psetnull
11561176

11571177
-- jsonb_insert
11581178
select jsonb_insert('{"a": [0,1,2]}','{a, 1}','"new_value"');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp