Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tkrYV-007Rfj-S3 for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Feb 2025 21:24:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tkrYT-00DpCb-8Z for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Feb 2025 21:24:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tkrYS-00DpCT-SG for pgsql-hackers@lists.postgresql.org; Wed, 19 Feb 2025 21:24:29 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tkrYQ-001pqV-1h for pgsql-hackers@lists.postgresql.org; Wed, 19 Feb 2025 21:24:28 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-aaecf50578eso55629466b.2 for ; Wed, 19 Feb 2025 13:24:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740000265; x=1740605065; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=egcGFso9fgu476QtSId4AqmEUP+orRiR5hqdM6HbcqE=; b=MhdlRiXFKICVt5sau4jQu8CT1WdIw5GClruGLq2/tu321Aj5NkuwYSpgIqAfsUdyeK +SumtuUTyJ7pirtWIdGEmRmzTSvw2RITLUtKkQ3HmDAkmZJFZssaQVSO0+2bseGguKfr OqJVW8iBLC+tEinEiCkEBdwlLxGarG0x0UAbzOYl/2tOQTYtttmn71E/2nWbpV9zgdBY HjjbqcAxrByltxIRowGLUwkKG89Rc4LxiXkmQOeec9f4qygFW44Xuw38HL8NiMTzHQKr 3DGSGNyZgUe1G867IptEHdI08LO/93qXDgvjib6WOMspNXFsl3DxQJ2LxuGnKtfbs8XV HMmQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740000265; x=1740605065; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=egcGFso9fgu476QtSId4AqmEUP+orRiR5hqdM6HbcqE=; b=iGAkvMXZJmBtye2shbhZRN175OIj5+zrgKt8oVdZ9+R2WhP/T2yiadnej8Q+sV+tkH 6Hr/q99i0aEJKwjyvgLdT4J/ZFaO9prqbHcydn7TjC9ESQ5O708577AG2HqW7IZwgAg9 aME2oy5tyw9kKgrKxH8xKkOIaWrjtvqdZ0qhS5YVAImhnGJk+msteE9Vj9inPWiRTURX I6IoQUvDC1YzbzBKuu6CEIUa4JmAohxktBZpL7O3hFTnTohpppz1eigMCi0CNSyihnAC PAdCA0E4cMM3kpvCM977c8/iQxYWvzOUm7U0IWDpDuDamB+miCu8jP5TeuJLmMBGZE8p Zz/Q== X-Gm-Message-State: AOJu0Yw6lHh+aBBkNZXvmkV9QL/cbLPVvby4LNbi0qUnFHlcjqiHZHbU SBoIpNIElnGDzULnm8W+CdrfImFwak9ImwzRYgadTpLRy1tTeDWq X-Gm-Gg: ASbGnctIOqS/iGfzSub/ez7l0NuTQ1uqt85DOs1j9psNLunyYxqq4ok9dPrT5IECD25 w8Mk0CiUA38dcY1mBC4fcolzoWVqjmQq0O0fS1gakvRHtHWbnOMuL1VeQqWEdtbsE84HbJGdA4T nH0ZV14tPnNay91gniC+EjtGq6udeIH0l8UzvvNIIwFfXdMPQZtptnxoo/arBfEhGfsc/EzcNIn ZNgGo3NQTlW04iD/JgSObNeM+qas+cFkaWAsUvjs5Q8iiXitXn60tMMhdb/s/Yt/2sttFrLJnl6 Wo3uDAQfPnquPCIA3kpcOdUvOGGn2m383LMtu++XLUzd8qRIp/o8QDWqAtc= X-Google-Smtp-Source: AGHT+IEryrRgZ5kQR0V1pCDJ1KOU2fkrsR82AR5eaQf3BIcMOEAQf8RhrSzEnxTYyc9GoAAmsbcMZA== X-Received: by 2002:a17:906:32d3:b0:ab7:b928:1e05 with SMTP id a640c23a62f3a-abb70921ad3mr1766890366b.4.1740000264599; Wed, 19 Feb 2025 13:24:24 -0800 (PST) Received: from smtpclient.apple (oym7vo.static.otenet.gr. [2.87.58.155]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-abbc3d4bed6sm350552466b.116.2025.02.19.13.24.22 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 19 Feb 2025 13:24:23 -0800 (PST) From: Florents Tselai Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_97ACDBDE-03C5-4A57-A958-70F9658BB571" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\)) Subject: Re: jsonb_strip_nulls with arrays? Date: Wed, 19 Feb 2025 23:23:51 +0200 In-Reply-To: Cc: pgsql-hackers To: Andrew Dunstan References: <4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com> <92de2543-de33-4092-9de7-b532a078353f@dunslane.net> <00e32809-ae7e-46cf-9b33-7323f5fc7784@dunslane.net> X-Mailer: Apple Mail (2.3826.400.131.1.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_97ACDBDE-03C5-4A57-A958-70F9658BB571 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 18 Jan 2025, at 11:51=E2=80=AFAM, Florents Tselai = wrote: >=20 >=20 >=20 >> On 8 Jan 2025, at 6:45=E2=80=AFPM, Andrew Dunstan = wrote: >>=20 >>=20 >>=20 >> On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote: >>>=20 >>>> We could, if we're going to do anything at all in this area. = Another possibility would be to provide a second optional parameter for = json{b}_strip_nulls. That's probably a better way to go. >>>>=20 >>> Here's a patch that adds that argument (only for jsonb; no json = implementation yet) >>>=20 >>>=20 >>=20 >> I think it looks sane. We're not stripping a top level null, which is = one thing I looked out for. >>=20 >> I think we need a json implementation as well, though. >>=20 >>=20 >>=20 > Thanks for having a Look, Andrew;=20 > if there aren=E2=80=99t any other objections, I=E2=80=99ll come back = with a json implementation too.=20 Attached is a v2 patch with the missing json implementation.=20 jsonb one remains the same. =EF=BF=BC=EF=BF=BC=EF=BF=BC >>=20 >> cheers >>=20 >>=20 >>=20 >> andrew >>=20 >>=20 >>=20 >> -- >> Andrew Dunstan >> EDB: https://www.enterprisedb.com --Apple-Mail=_97ACDBDE-03C5-4A57-A958-70F9658BB571 Content-Type: multipart/mixed; boundary="Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163" --Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 18 Jan 2025, at 11:51=E2=80=AFAM, Florents Tselai = <florents.tselai@gmail.com> wrote:



On = 8 Jan 2025, at 6:45=E2=80=AFPM, Andrew Dunstan = <andrew@dunslane.net> wrote:


On 2024-09-17 Tu 4:53 PM, Florents Tselai = wrote:

We could, if we're going = to do anything at all in this area. Another possibility would be to = provide a second optional parameter for json{b}_strip_nulls. That's = probably a better way to go.

Here's a patch = that adds that argument (only for jsonb; no json implementation = yet)



I = think it looks sane. We're not stripping a top level null, which is one = thing I looked out for.

I think we need a json implementation as = well, though.


Thanks for having a = Look, Andrew; 
if there aren=E2=80=99t any other objections, = I=E2=80=99ll come back with a json implementation = too. 

Attached is a = v2 patch with the missing json implementation. 
jsonb one = remains the same.

= --Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Disposition: attachment; filename=v2-0001-jsonb_strip_nulls-jsonb-bool-wip.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-jsonb_strip_nulls-jsonb-bool-wip.patch" Content-Transfer-Encoding: quoted-printable =46rom=20cd7a43911daeae9af09a5163c95a2c4c3268f497=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Florents=20Tselai=20= =0ADate:=20Tue,=2017=20Sep=202024=2022:05:58=20= +0300=0ASubject:=20[PATCH=20v2=201/3]=20jsonb_strip_nulls(jsonb,=20bool)=20= wip=0A=0AImplementation=20and=20passing=20tests=20for=20= jsonb_strip_nulls(jsonb,=20bool)=0Ano=20docs=20yet.=0A---=0A=20= src/backend/catalog/system_functions.sql=20|=20=207=20++++=0A=20= src/backend/utils/adt/jsonfuncs.c=20=20=20=20=20=20=20=20|=2011=20+++++-=0A= =20src/include/catalog/pg_proc.dat=20=20=20=20=20=20=20=20=20=20|=20=202=20= +-=0A=20src/test/regress/expected/jsonb.out=20=20=20=20=20=20|=2050=20= ++++++++++++++++++++++++=0A=20src/test/regress/sql/jsonb.sql=20=20=20=20=20= =20=20=20=20=20=20|=2018=20+++++++++=0A=205=20files=20changed,=2086=20= insertions(+),=202=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/catalog/system_functions.sql=20= b/src/backend/catalog/system_functions.sql=0Aindex=20= 591157b1d1b..54bf061036f=20100644=0A---=20= a/src/backend/catalog/system_functions.sql=0A+++=20= b/src/backend/catalog/system_functions.sql=0A@@=20-607,6=20+607,13=20@@=20= LANGUAGE=20INTERNAL=0A=20STRICT=20STABLE=20PARALLEL=20SAFE=0A=20AS=20= 'jsonb_path_query_first_tz';=0A=20=0A+CREATE=20OR=20REPLACE=20FUNCTION=0A= +=20=20jsonb_strip_nulls(target=20jsonb,=20strip_in_arrays=20boolean=20= DEFAULT=20false)=0A+RETURNS=20jsonb=0A+LANGUAGE=20INTERNAL=0A+STRICT=20= STABLE=20PARALLEL=20SAFE=0A+AS=20'jsonb_strip_nulls';=0A+=0A=20--=20= default=20normalization=20form=20is=20NFC,=20per=20SQL=20standard=0A=20= CREATE=20OR=20REPLACE=20FUNCTION=0A=20=20=20"normalize"(text,=20text=20= DEFAULT=20'NFC')=0Adiff=20--git=20a/src/backend/utils/adt/jsonfuncs.c=20= b/src/backend/utils/adt/jsonfuncs.c=0Aindex=20c2e90f1a3bf..cc74fa55a11=20= 100644=0A---=20a/src/backend/utils/adt/jsonfuncs.c=0A+++=20= b/src/backend/utils/adt/jsonfuncs.c=0A@@=20-4520,12=20+4520,13=20@@=20= json_strip_nulls(PG_FUNCTION_ARGS)=0A=20}=0A=20=0A=20/*=0A-=20*=20SQL=20= function=20jsonb_strip_nulls(jsonb)=20->=20jsonb=0A+=20*=20SQL=20= function=20jsonb_strip_nulls(jsonb,=20bool)=20->=20jsonb=0A=20=20*/=0A=20= Datum=0A=20jsonb_strip_nulls(PG_FUNCTION_ARGS)=0A=20{=0A=20=09Jsonb=09=20= =20=20*jb=20=3D=20PG_GETARG_JSONB_P(0);=0A+=09bool=09=09strip_in_arrays=20= =3D=20false;=0A=20=09JsonbIterator=20*it;=0A=20=09JsonbParseState=20= *parseState=20=3D=20NULL;=0A=20=09JsonbValue=20*res=20=3D=20NULL;=0A@@=20= -4534,6=20+4535,9=20@@=20jsonb_strip_nulls(PG_FUNCTION_ARGS)=0A=20=09= JsonbIteratorToken=20type;=0A=20=09bool=09=09last_was_key=20=3D=20false;=0A= =20=0A+=09if=20(PG_NARGS()=20=3D=3D=202)=0A+=09=09strip_in_arrays=20=3D=20= PG_GETARG_BOOL(1);=0A+=0A=20=09if=20(JB_ROOT_IS_SCALAR(jb))=0A=20=09=09= PG_RETURN_POINTER(jb);=0A=20=0A@@=20-4564,6=20+4568,11=20@@=20= jsonb_strip_nulls(PG_FUNCTION_ARGS)=0A=20=09=09=09(void)=20= pushJsonbValue(&parseState,=20WJB_KEY,=20&k);=0A=20=09=09}=0A=20=0A+=09=09= /*=20if=20strip_in_arrays=20is=20set,=20also=20skip=20null=20array=20= elements=20*/=0A+=09=09if=20(strip_in_arrays)=0A+=09=09=09if=20(type=20= =3D=3D=20WJB_ELEM=20&&=20v.type=20=3D=3D=20jbvNull)=0A+=09=09=09=09= continue;=0A+=0A=20=09=09if=20(type=20=3D=3D=20WJB_VALUE=20||=20type=20= =3D=3D=20WJB_ELEM)=0A=20=09=09=09res=20=3D=20pushJsonbValue(&parseState,=20= type,=20&v);=0A=20=09=09else=0Adiff=20--git=20= a/src/include/catalog/pg_proc.dat=20b/src/include/catalog/pg_proc.dat=0A= index=20b37e8a6f882..0a812d6a23b=20100644=0A---=20= a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-10174,7=20+10174,7=20@@=0A=20=20= =20prorettype=20=3D>=20'jsonb',=20proargtypes=20=3D>=20'',=0A=20=20=20= prosrc=20=3D>=20'jsonb_build_object_noargs'=20},=0A=20{=20oid=20=3D>=20= '3262',=20descr=20=3D>=20'remove=20object=20fields=20with=20null=20= values=20from=20jsonb',=0A-=20=20proname=20=3D>=20'jsonb_strip_nulls',=20= prorettype=20=3D>=20'jsonb',=20proargtypes=20=3D>=20'jsonb',=0A+=20=20= proname=20=3D>=20'jsonb_strip_nulls',=20prorettype=20=3D>=20'jsonb',=20= proargtypes=20=3D>=20'jsonb=20bool',=0A=20=20=20prosrc=20=3D>=20= 'jsonb_strip_nulls'=20},=0A=20=0A=20{=20oid=20=3D>=20'3478',=0Adiff=20= --git=20a/src/test/regress/expected/jsonb.out=20= b/src/test/regress/expected/jsonb.out=0Aindex=207d163a156e3..e55ed196e84=20= 100644=0A---=20a/src/test/regress/expected/jsonb.out=0A+++=20= b/src/test/regress/expected/jsonb.out=0A@@=20-4153,6=20+4153,56=20@@=20= select=20jsonb_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20= {}=20}');=0A=20=20{"a":=20{},=20"d":=20{}}=0A=20(1=20row)=0A=20=0A+--=20= jsonb_strip_nulls=20(strip_in_arrays=3Dtrue)=0A+select=20= jsonb_strip_nulls(null,=20true);=0A+=20jsonb_strip_nulls=20=0A= +-------------------=0A+=20=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('1',=20true);=0A+=20jsonb_strip_nulls=20=0A= +-------------------=0A+=201=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('"a=20string"',=20true);=0A+=20jsonb_strip_nulls=20=0A= +-------------------=0A+=20"a=20string"=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('null',=20true);=0A+=20jsonb_strip_nulls=20=0A= +-------------------=0A+=20null=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('[1,2,null,3,4]',=20true);=0A+=20jsonb_strip_nulls=20=0A= +-------------------=0A+=20[1,=202,=203,=204]=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}',=20= true);=0A+=20=20=20=20=20=20=20=20=20=20jsonb_strip_nulls=20=20=20=20=20=20= =20=20=20=20=20=0A+--------------------------------------=0A+=20{"a":=20= 1,=20"c":=20[2,=203],=20"d":=20{"e":=204}}=0A+(1=20row)=0A+=0A+select=20= jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]',=20true);=0A+=20=20=20=20= jsonb_strip_nulls=20=20=20=20=20=0A+--------------------------=0A+=20[1,=20= {"a":=201,=20"c":=202},=203]=0A+(1=20row)=0A+=0A+--=20an=20empty=20= object=20is=20not=20null=20and=20should=20not=20be=20stripped=0A+select=20= jsonb_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20{}=20= }',=20true);=0A+=20jsonb_strip_nulls=20=20=0A+--------------------=0A+=20= {"a":=20{},=20"d":=20{}}=0A+(1=20row)=0A+=0A=20select=20= jsonb_pretty('{"a":=20"test",=20"b":=20[1,=202,=203],=20"c":=20"test3",=20= "d":{"dd":=20"test4",=20"dd2":{"ddd":=20"test5"}}}');=0A=20=20=20=20=20=20= =20=20=20jsonb_pretty=20=20=20=20=20=20=20=20=0A=20= ----------------------------=0Adiff=20--git=20= a/src/test/regress/sql/jsonb.sql=20b/src/test/regress/sql/jsonb.sql=0A= index=205f0190d5a2b..a6eee0da3cb=20100644=0A---=20= a/src/test/regress/sql/jsonb.sql=0A+++=20= b/src/test/regress/sql/jsonb.sql=0A@@=20-1102,6=20+1102,24=20@@=20select=20= jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');=0A=20--=20an=20empty=20= object=20is=20not=20null=20and=20should=20not=20be=20stripped=0A=20= select=20jsonb_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20= {}=20}');=0A=20=0A+--=20jsonb_strip_nulls=20(strip_in_arrays=3Dtrue)=0A+=0A= +select=20jsonb_strip_nulls(null,=20true);=0A+=0A+select=20= jsonb_strip_nulls('1',=20true);=0A+=0A+select=20jsonb_strip_nulls('"a=20= string"',=20true);=0A+=0A+select=20jsonb_strip_nulls('null',=20true);=0A= +=0A+select=20jsonb_strip_nulls('[1,2,null,3,4]',=20true);=0A+=0A+select=20= jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}',=20= true);=0A+=0A+select=20jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]',=20= true);=0A+=0A+--=20an=20empty=20object=20is=20not=20null=20and=20should=20= not=20be=20stripped=0A+select=20jsonb_strip_nulls('{"a":=20{"b":=20null,=20= "c":=20null},=20"d":=20{}=20}',=20true);=0A=20=0A=20select=20= jsonb_pretty('{"a":=20"test",=20"b":=20[1,=202,=203],=20"c":=20"test3",=20= "d":{"dd":=20"test4",=20"dd2":{"ddd":=20"test5"}}}');=0A=20select=20= jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');=0A--=20= =0A2.48.1=0A=0A= --Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii
--Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Disposition: attachment; filename=v2-0002-Add-docs-for-strip_in_arrays-argument.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0002-Add-docs-for-strip_in_arrays-argument.patch" Content-Transfer-Encoding: quoted-printable =46rom=209672e714371e7fdf12b0c58fe0b355e2a8d1ebd2=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Florents=20Tselai=20= =0ADate:=20Tue,=2017=20Sep=202024=2023:38:01=20= +0300=0ASubject:=20[PATCH=20v2=202/3]=20Add=20docs=20for=20= strip_in_arrays=20argument=0A=0A---=0A=20doc/src/sgml/func.sgml=20|=2012=20= +++++++++---=0A=201=20file=20changed,=209=20insertions(+),=203=20= deletions(-)=0A=0Adiff=20--git=20a/doc/src/sgml/func.sgml=20= b/doc/src/sgml/func.sgml=0Aindex=2047370e581ae..129103323c5=20100644=0A= ---=20a/doc/src/sgml/func.sgml=0A+++=20b/doc/src/sgml/func.sgml=0A@@=20= -17179,25=20+17179,31=20@@=20ERROR:=20=20value=20too=20long=20for=20type=20= character(2)=0A=20=20=20=20=20=20=20=20=20=0A=20=20=20=20=20=20= =20=20=20=20json_strip_nulls=0A=20=20=20=20=20=20=20=20= =20=0A-=20=20=20=20=20=20=20=20= json_strip_nulls=20(=20json=20)=0A+=20=20= =20=20=20=20=20=20json_strip_nulls=20(=20= target=20jsonb,=20= ,strip_in_arrays=20boolean=20= =20)=0A=20=20=20=20=20=20=20=20=20= json=0A=20=20=20=20=20=20=20=20=0A=20=20= =20=20=20=20=20=20=0A=20=20=20=20=20=20=20= =20=20=0A=20=20=20=20=20=20=20=20=20=20= jsonb_strip_nulls=0A=20=20=20=20=20=20=20=20=20= =0A-=20=20=20=20=20=20=20=20= jsonb_strip_nulls=20(=20jsonb=20)=0A+=20= =20=20=20=20=20=20=20jsonb_strip_nulls=20(=20= target=20jsonb,=20= ,strip_in_arrays=20boolean=20= =20)=0A=20=20=20=20=20=20=20=20=20= jsonb=0A=20=20=20=20=20=20=20=20=0A=20=20= =20=20=20=20=20=20=0A=20=20=20=20=20=20=20=20=20Deletes=20all=20= object=20fields=20that=20have=20null=20values=20from=20the=20given=20= JSON=0A=20=20=20=20=20=20=20=20=20value,=20recursively.=20=20Null=20= values=20that=20are=20not=20object=20fields=20are=0A=20=20=20=20=20=20=20= =20=20untouched.=0A+=20=20=20=20=20=20=20=20If=20= strip_in_arrays=20is=20true=20(default=20is=20= false),=20null=20array=20elements=20are=20also=20stripped.=0A=20=20=20=20= =20=20=20=20=0A=20=20=20=20=20=20=20=20=0A=20=20=20=20=20=20= =20=20=20json_strip_nulls('[{"f1":1,=20"f2":null},=202,=20null,=20= 3]')=0A=20=20=20=20=20=20=20=20=20= [{"f1":1},2,null,3]=0A-=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=20= jsonb_strip_nulls('[1,2,null,3,4]',=20true);=0A+=20=20= =20=20=20=20=20=20[1,2,3,4]=0A+=20=20=20=20=20= =20=20=0A+=20=20=20=20=20=20=20=0A=20=20=20=20=20=20=20= =0A=20=0A=20=20=20=20=20=20=20=0A--=20=0A2.48.1=0A=0A= --Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii
--Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Disposition: attachment; filename=v2-0003-Add-implementation-for-json_strip_nulls-json-bool.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0003-Add-implementation-for-json_strip_nulls-json-bool.patch" Content-Transfer-Encoding: quoted-printable =46rom=2042664c0db2032b491b1e9926748d367c421540d0=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Florents=20Tselai=20= =0ADate:=20Wed,=2019=20Feb=202025=2023:12:58=20= +0200=0ASubject:=20[PATCH=20v2=203/3]=20Add=20implementation=20for=20= json_strip_nulls(json,bool)=0A=0A---=0A=20= src/backend/catalog/system_functions.sql=20|=20=207=20++++=0A=20= src/backend/utils/adt/jsonfuncs.c=20=20=20=20=20=20=20=20|=2016=20= +++++++-=0A=20src/include/catalog/pg_proc.dat=20=20=20=20=20=20=20=20=20=20= |=20=202=20+-=0A=20src/test/regress/expected/json.out=20=20=20=20=20=20=20= |=2050=20++++++++++++++++++++++++=0A=20src/test/regress/sql/json.sql=20=20= =20=20=20=20=20=20=20=20=20=20|=2019=20+++++++++=0A=205=20files=20= changed,=2092=20insertions(+),=202=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/catalog/system_functions.sql=20= b/src/backend/catalog/system_functions.sql=0Aindex=20= 54bf061036f..60306e09f0f=20100644=0A---=20= a/src/backend/catalog/system_functions.sql=0A+++=20= b/src/backend/catalog/system_functions.sql=0A@@=20-614,6=20+614,13=20@@=20= LANGUAGE=20INTERNAL=0A=20STRICT=20STABLE=20PARALLEL=20SAFE=0A=20AS=20= 'jsonb_strip_nulls';=0A=20=0A+CREATE=20OR=20REPLACE=20FUNCTION=0A+=20=20= json_strip_nulls(target=20json,=20strip_in_arrays=20boolean=20DEFAULT=20= false)=0A+RETURNS=20json=0A+LANGUAGE=20INTERNAL=0A+STRICT=20STABLE=20= PARALLEL=20SAFE=0A+AS=20'json_strip_nulls';=0A+=0A=20--=20default=20= normalization=20form=20is=20NFC,=20per=20SQL=20standard=0A=20CREATE=20OR=20= REPLACE=20FUNCTION=0A=20=20=20"normalize"(text,=20text=20DEFAULT=20= 'NFC')=0Adiff=20--git=20a/src/backend/utils/adt/jsonfuncs.c=20= b/src/backend/utils/adt/jsonfuncs.c=0Aindex=20cc74fa55a11..9f43b58dba5=20= 100644=0A---=20a/src/backend/utils/adt/jsonfuncs.c=0A+++=20= b/src/backend/utils/adt/jsonfuncs.c=0A@@=20-286,6=20+286,7=20@@=20= typedef=20struct=20StripnullState=0A=20=09JsonLexContext=20*lex;=0A=20=09= StringInfo=09strval;=0A=20=09bool=09=09skip_next_null;=0A+=09bool=09=09= strip_in_arrays;=0A=20}=20StripnullState;=0A=20=0A=20/*=20structure=20= for=20generalized=20json/jsonb=20value=20passing=20*/=0A@@=20-4460,8=20= +4461,19=20@@=20sn_array_element_start(void=20*state,=20bool=20isnull)=0A= =20{=0A=20=09StripnullState=20*_state=20=3D=20(StripnullState=20*)=20= state;=0A=20=0A-=09if=20(_state->strval->data[_state->strval->len=20-=20= 1]=20!=3D=20'[')=0A+=09/*=20If=20strip_in_arrays=20is=20enabled=20and=20= this=20is=20a=20null,=20mark=20it=20for=20skipping=20*/=0A+=09if=20= (isnull=20&&=20_state->strip_in_arrays)=0A+=09{=0A+=09=09= _state->skip_next_null=20=3D=20true;=0A+=09=09return=20JSON_SUCCESS;=0A+=09= }=0A+=0A+=09/*=20Only=20add=20a=20comma=20if=20this=20is=20not=20the=20= first=20valid=20element=20*/=0A+=09if=20(_state->strval->len=20>=200=20= &&=0A+=09=09_state->strval->data[_state->strval->len=20-=201]=20!=3D=20= '[')=0A+=09{=0A=20=09=09appendStringInfoCharMacro(_state->strval,=20= ',');=0A+=09}=0A=20=0A=20=09return=20JSON_SUCCESS;=0A=20}=0A@@=20-4493,6=20= +4505,7=20@@=20Datum=0A=20json_strip_nulls(PG_FUNCTION_ARGS)=0A=20{=0A=20= =09text=09=20=20=20*json=20=3D=20PG_GETARG_TEXT_PP(0);=0A+=09bool=09=09= strip_in_arrays=20=3D=20PG_NARGS()=20=3D=3D=202=20?=20PG_GETARG_BOOL(1)=20= :=20false;=0A=20=09StripnullState=20*state;=0A=20=09JsonLexContext=20= lex;=0A=20=09JsonSemAction=20*sem;=0A@@=20-4503,6=20+4516,7=20@@=20= json_strip_nulls(PG_FUNCTION_ARGS)=0A=20=09state->lex=20=3D=20= makeJsonLexContext(&lex,=20json,=20true);=0A=20=09state->strval=20=3D=20= makeStringInfo();=0A=20=09state->skip_next_null=20=3D=20false;=0A+=09= state->strip_in_arrays=20=3D=20strip_in_arrays;=0A=20=0A=20=09= sem->semstate=20=3D=20state;=0A=20=09sem->object_start=20=3D=20= sn_object_start;=0Adiff=20--git=20a/src/include/catalog/pg_proc.dat=20= b/src/include/catalog/pg_proc.dat=0Aindex=200a812d6a23b..d0ebb4b8541=20= 100644=0A---=20a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-9238,7=20+9238,7=20@@=0A=20=20=20= proname=20=3D>=20'to_json',=20provolatile=20=3D>=20's',=20prorettype=20= =3D>=20'json',=0A=20=20=20proargtypes=20=3D>=20'anyelement',=20prosrc=20= =3D>=20'to_json'=20},=0A=20{=20oid=20=3D>=20'3261',=20descr=20=3D>=20= 'remove=20object=20fields=20with=20null=20values=20from=20json',=0A-=20=20= proname=20=3D>=20'json_strip_nulls',=20prorettype=20=3D>=20'json',=20= proargtypes=20=3D>=20'json',=0A+=20=20proname=20=3D>=20= 'json_strip_nulls',=20prorettype=20=3D>=20'json',=20proargtypes=20=3D>=20= 'json=20bool',=0A=20=20=20prosrc=20=3D>=20'json_strip_nulls'=20},=0A=20=0A= =20{=20oid=20=3D>=20'3947',=0Adiff=20--git=20= a/src/test/regress/expected/json.out=20= b/src/test/regress/expected/json.out=0Aindex=2096c40911cb9..04b478cb468=20= 100644=0A---=20a/src/test/regress/expected/json.out=0A+++=20= b/src/test/regress/expected/json.out=0A@@=20-2504,6=20+2504,56=20@@=20= select=20json_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20= {}=20}');=0A=20=20{"a":{},"d":{}}=0A=20(1=20row)=0A=20=0A+--=20= json_strip_nulls=20(strip_in_arrays=3Dtrue)=0A+select=20= json_strip_nulls(null,=20true);=0A+=20json_strip_nulls=20=0A= +------------------=0A+=20=0A+(1=20row)=0A+=0A+select=20= json_strip_nulls('1',=20true);=0A+=20json_strip_nulls=20=0A= +------------------=0A+=201=0A+(1=20row)=0A+=0A+select=20= json_strip_nulls('"a=20string"',=20true);=0A+=20json_strip_nulls=20=0A= +------------------=0A+=20"a=20string"=0A+(1=20row)=0A+=0A+select=20= json_strip_nulls('null',=20true);=0A+=20json_strip_nulls=20=0A= +------------------=0A+=20null=0A+(1=20row)=0A+=0A+select=20= json_strip_nulls('[1,2,null,3,4]',=20true);=0A+=20json_strip_nulls=20=0A= +------------------=0A+=20[1,2,3,4]=0A+(1=20row)=0A+=0A+select=20= json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}',=20= true);=0A+=20=20=20=20=20=20=20json_strip_nulls=20=20=20=20=20=20=20=20=0A= +-------------------------------=0A+=20{"a":1,"c":[2,3],"d":{"e":4}}=0A= +(1=20row)=0A+=0A+select=20= json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]',=20true);=0A+=20=20= json_strip_nulls=20=20=20=0A+---------------------=0A+=20= [1,{"a":1,"c":2},3]=0A+(1=20row)=0A+=0A+--=20an=20empty=20object=20is=20= not=20null=20and=20should=20not=20be=20stripped=0A+select=20= json_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20{}=20= }',=20true);=0A+=20json_strip_nulls=20=0A+------------------=0A+=20= {"a":{},"d":{}}=0A+(1=20row)=0A+=0A=20--=20json=20to=20tsvector=0A=20= select=20to_tsvector('{"a":=20"aaa=20bbb=20ddd=20ccc",=20"b":=20["eee=20= fff=20ggg"],=20"c":=20{"d":=20"hhh=20iii"}}'::json);=0A=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20to_tsvector=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=0Adiff=20--git=20= a/src/test/regress/sql/json.sql=20b/src/test/regress/sql/json.sql=0A= index=208251f4f4006..e9800b21ffe=20100644=0A---=20= a/src/test/regress/sql/json.sql=0A+++=20b/src/test/regress/sql/json.sql=0A= @@=20-814,6=20+814,25=20@@=20select=20= json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');=0A=20--=20an=20empty=20= object=20is=20not=20null=20and=20should=20not=20be=20stripped=0A=20= select=20json_strip_nulls('{"a":=20{"b":=20null,=20"c":=20null},=20"d":=20= {}=20}');=0A=20=0A+--=20json_strip_nulls=20(strip_in_arrays=3Dtrue)=0A+=0A= +select=20json_strip_nulls(null,=20true);=0A+=0A+select=20= json_strip_nulls('1',=20true);=0A+=0A+select=20json_strip_nulls('"a=20= string"',=20true);=0A+=0A+select=20json_strip_nulls('null',=20true);=0A+=0A= +select=20json_strip_nulls('[1,2,null,3,4]',=20true);=0A+=0A+select=20= json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}',=20= true);=0A+=0A+select=20json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]',=20= true);=0A+=0A+--=20an=20empty=20object=20is=20not=20null=20and=20should=20= not=20be=20stripped=0A+select=20json_strip_nulls('{"a":=20{"b":=20null,=20= "c":=20null},=20"d":=20{}=20}',=20true);=0A+=0A=20--=20json=20to=20= tsvector=0A=20select=20to_tsvector('{"a":=20"aaa=20bbb=20ddd=20ccc",=20= "b":=20["eee=20fff=20ggg"],=20"c":=20{"d":=20"hhh=20iii"}}'::json);=0A=20= =0A--=20=0A2.48.1=0A=0A= --Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

--Apple-Mail=_7474F8F4-026C-4D29-93E3-713F60A22163-- --Apple-Mail=_97ACDBDE-03C5-4A57-A958-70F9658BB571--