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.96) (envelope-from ) id 1w9oCy-001mG1-2e for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Apr 2026 17:57:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w9oCx-00B1d4-0z for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Apr 2026 17:57:55 +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.96) (envelope-from ) id 1w9oCw-00B1cw-2y for pgsql-hackers@lists.postgresql.org; Mon, 06 Apr 2026 17:57:55 +0000 Received: from mail-yx1-xb131.google.com ([2607:f8b0:4864:20::b131]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w9oCu-00000000wKj-4ASa for pgsql-hackers@lists.postgresql.org; Mon, 06 Apr 2026 17:57:55 +0000 Received: by mail-yx1-xb131.google.com with SMTP id 956f58d0204a3-64edf260b49so5872679d50.0 for ; Mon, 06 Apr 2026 10:57:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775498271; x=1776103071; 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=8GZvw6dKqXH1wVhAdCECtaNGWdD49rGNiJ7MQm5MJkQ=; b=FMhjEyymQhZUwNISz7NCixx3Zc3luJmxCyRNDDeC+U7KhAMUTRS8Xtb/0kqm+3EVt+ MsGs7h9XDJxCoeAdCqN2f8MsFA7OczeAtnkifMFdp/qd4Upc6ddWzcLdTcLVJohDslWk hxW4aFHKPiA5JjoguX/FQUKSacauNWG4C95zYVz81p+oOnookrsTavyxIvbSKWhd1wjO m2I6KH0Nmpw5pkyvhuHJMTx6ihcn35EFOJ3mthDahWVjHkRw7HAQT+wVrGL6oBmtSMki FkTg5mYu2MHrpjI+59WiU5kvLYLqsX47khfVwWFRGgo0zdCgsV6pkoWpLKawUmK8qWKZ L+1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775498271; x=1776103071; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=8GZvw6dKqXH1wVhAdCECtaNGWdD49rGNiJ7MQm5MJkQ=; b=pfp/IIO6AyZ8yri/k7iM2bLYjzsp2r3ktL74fs8pRyHJLcwWDN0AmDmKj9qikjcT/8 OpvVjbxilIeWxot3UWRPOK3KklyeKdFf8J75oYczWiDRiI7og1w0gPK/Uu9ve6Bz24JG kmDo1pjDV/e1ECwYsZNBHCAN6lOrzc/qbowC3B6zATa/RkvxDJ9+e4sGQOKKhHGoIhie LI3l7ld0QzLVUA98N7mWt6d6SprJVQ0k+w2RSsTwa3d1UAPmegiF+vdGJM3F29zGcuC8 PODmxUOn2fFWnpgAcbo87hYBKms+yPqL5Gnox+I/ftym/EsS7GTlsQtK/X5QI7WtA8Th xkbw== X-Forwarded-Encrypted: i=1; AJvYcCX70xPWHKaRgaRyoCIu4wjnLsEdfCPsRwhjlO+RYP5jUVmsBIfE3YNo57LmnLDrgVufj7NRmtYVgDN2WA3E@lists.postgresql.org X-Gm-Message-State: AOJu0YwSwqUD2ydb2G19M5dQUlgmPWrrPb86EehyJG6c9A06Qx5O3MMK +D9EdMcHbsew9d7wqO4T8dVV+3kVrxURMDmNmnEXXxU0el9ItR27K2UZ X-Gm-Gg: AeBDievNQVQfGwIMJgFbUprauMHnHafrdFDDTTXyGkA67c5kzMdkm2hPJ27ygp+ebwG UIr7TjsehFxACxHC2HyLDuGk/0atxTuh6Idi1AX5J56fODs0gDf7YuaGyjKsbXnUXEvtXdBHHEt 2IKX1oK6N9LJBl8/x+wDC85Xx6bJsNh0D918qPh+YqH4LRmSPqvIjtP+J/K+YhIFwkyV159sDnB XcSx48ed6Ii7GL8E8QVihTHrYX4RN6LdZ87pYnntlRYgRjQuOnWmW8FV/9wqKkyBB1v202Qzf+V UhjEGb6Ikg5eDE8YKUUuPcUFwWFReV3b9Cfp3uC+0kR17ctCozcRMkiZCLE8YaYPt4DEbpq5LXQ 497V35gFZO7mwLKXBdcKS4VpPEpHtZJuKRoA2/vbSfuFZ7Hgd3SQEGqfS+Klkd7Xw6d3jNS4GLi B5aQIN/Bjod+t40rUN8AeFq5Dobf+WM2bmL5QHIt1BKUcyu7Tr3LmORfYv2lfEOl29ew== X-Received: by 2002:a05:690e:130e:b0:650:1cbb:50a8 with SMTP id 956f58d0204a3-6503d84d2e8mr13498270d50.26.1775498271372; Mon, 06 Apr 2026 10:57:51 -0700 (PDT) Received: from smtpclient.apple ([2620:149:13d0:100::b6]) by smtp.gmail.com with ESMTPSA id 956f58d0204a3-6503a9e916esm6269444d50.21.2026.04.06.10.57.49 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 06 Apr 2026 10:57:50 -0700 (PDT) From: Haibo Yan X-Google-Original-From: Haibo Yan Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_09156B8A-76C8-4F65-931E-87B8D096919B" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.500.181\)) Subject: Re: Extract numeric filed in JSONB more effectively Date: Mon, 6 Apr 2026 10:57:38 -0700 In-Reply-To: <2C8D5325-71FB-4A3E-906D-C56C0487AC17@gmail.com> Cc: David Rowley , Andy Fan , Peter Eisentraut , Amit Langote , Alvaro Herrera , jian he , Chapman Flack , pgsql-hackers@lists.postgresql.org To: Dmitry Dolgov <9erthalion6@gmail.com> References: <875xx197bp.fsf@163.com> <87ttk0lgcx.fsf@163.com> <8734m5fua1.fsf@163.com> <87a5dx4cfb.fsf@163.com> <7487A577-7BDC-4B94-9FFA-741E95439BFC@gmail.com> <40442A5A-C7BF-4742-9F88-1068686EC174@gmail.com> <2C8D5325-71FB-4A3E-906D-C56C0487AC17@gmail.com> X-Mailer: Apple Mail (2.3864.500.181) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_09156B8A-76C8-4F65-931E-87B8D096919B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Apr 6, 2026, at 10:51=E2=80=AFAM, Haibo Yan = wrote: >=20 >=20 >> On Apr 6, 2026, at 6:05=E2=80=AFAM, Dmitry Dolgov = <9erthalion6@gmail.com> wrote: >>=20 >>> On Fri, Apr 03, 2026 at 09:42:20AM -0700, Haibo Yan wrote: >>=20 >> Thanks for picking it up, the patch looks good. >>=20 >>> One small thing: the UI looks a bit odd on my side. It does not seem = to have picked my attachment, and is instead showing your older = attachment there. But cfbot appears to have picked up mine, so I think = it may just be a UI issue. >>=20 >> It takes some time, as far as I can see currently the correct patch = is shown. >>=20 >>> + /* >>> + * Identify the inner extraction expression. It may = appear as >>> + * either a FuncExpr or an OpExpr; accept both forms. >>> + */ >>> + if (IsA(arg, FuncExpr)) >>> + { >>> + FuncExpr *inner =3D (FuncExpr *) arg; >>> + >>> + inner_funcid =3D inner->funcid; >>> + inner_args =3D inner->args; >>> + location =3D inner->location; >>> + } >>> + else if (IsA(arg, OpExpr)) >>> + { >>> + OpExpr *inner =3D (OpExpr *) arg; >>> + >>> + inner_funcid =3D inner->opfuncid; >>> + inner_args =3D inner->args; >>> + location =3D inner->location; >>> + } >>> + else >>> + PG_RETURN_POINTER(NULL); >>=20 >> It may also appear as a SubscriptingRef expression if we use = subscription over >> jsonb. >>=20 >> SELECT test_json['field7']::bool FROM test_jsonb WHERE json_type = =3D 'object'; >>=20 >> Seems to be worth handling this case as well, since it doesn't lead = to an >> interface explosion. >=20 >=20 >=20 > Thank you Dmitry > I agree that handling SubscriptingRef here is the right thing to do. >=20 > It stays within the same stage-1 scope, covers an equivalent = object-field extraction form, and does not introduce any additional = interface surface. Based on your suggestion, I updated the patch to = recognize the SubscriptingRefrepresentation as well, so cases like: >=20 > ---------------------------------------- >=20 > test_json['field7']::bool >=20 > test_json['field4']::numeric >=20 > ---------------------------------------- >=20 > can follow the same rewrite path as the existing = jsonb_object_field(...) / -> cases. >=20 > Please see the updated patch: >=20 >=20 > >=20 > Thanks again for the review and suggestion. >=20 > Regards, >=20 > Haibo >=20 It looks like the attachment may have been dropped in transit. I=E2=80=99m= reattaching it here. Regards, Haibo =EF=BF=BC= --Apple-Mail=_09156B8A-76C8-4F65-931E-87B8D096919B Content-Type: multipart/mixed; boundary="Apple-Mail=_83434D77-760B-42C1-A4EB-0BCAFD7A1640" --Apple-Mail=_83434D77-760B-42C1-A4EB-0BCAFD7A1640 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Apr 6, 2026, at 10:51=E2=80=AFAM, Haibo Yan = <Tristan.Yim@gmail.com> wrote:


On Apr 6, = 2026, at 6:05=E2=80=AFAM, Dmitry Dolgov <9erthalion6@gmail.com> = wrote:

On Fri, Apr 03, 2026 at 09:42:20AM -0700, Haibo Yan = wrote:

Thanks for picking it up, the patch looks = good.

One small thing: the UI looks a = bit odd on my side. It does not seem to have picked my attachment, and = is instead showing your older attachment there. But cfbot appears to = have picked up mine, so I think it may just be a UI = issue.

It takes some time, as far as I can see = currently the correct patch is shown.

+ = = /*
+ = = * Identify the inner extraction expression.  It may appear = as
+ = = * either a FuncExpr or an OpExpr; accept both forms.
+ = */
+ = = if (IsA(arg, FuncExpr))
+ {
+ FuncExpr =   *inner =3D (FuncExpr *) arg;
+
+ = inner_funcid =3D inner->funcid;
+ = inner_args =3D inner->args;
+ location =3D = inner->location;
+ }
+ else if = (IsA(arg, OpExpr))
+ {
+ = OpExpr =   *inner =3D (OpExpr *) arg;
+
+ = inner_funcid =3D inner->opfuncid;
+ = inner_args =3D inner->args;
+ location =3D = inner->location;
+ }
+ = else
+ = = = PG_RETURN_POINTER(NULL);

It may also appear = as a SubscriptingRef expression if we use subscription = over
jsonb.

SELECT test_json['field7']::bool = FROM test_jsonb WHERE json_type =3D 'object';

Seems to be worth = handling this case as well, since it doesn't lead to an
interface = explosion.


Thank you Dmitry

I agree that = handling SubscriptingRef here is the = right thing to do.

It stays within the same stage-1 = scope, covers an equivalent object-field extraction form, and does not = introduce any additional interface surface. Based on your suggestion, I = updated the patch to recognize the SubscriptingRefrepresentation as well, so cases = like:

----------------------------------------

test_json['field7']::bool

test_json['field4']::numeric

----------------------------------------

can follow the same rewrite path as the existing jsonb_object_field(...) / -> cases.

Please see the = updated patch:


<v2-0001-jsonb-optimize= -object-field-casts-to-numeric-and-.patch>

Thanks again = for the review and suggestion.

Regards,

Haibo

It looks like the attachment may have been dropped in = transit. I=E2=80=99m reattaching it here.

Regards,

Haibo


= --Apple-Mail=_83434D77-760B-42C1-A4EB-0BCAFD7A1640 Content-Disposition: attachment; filename*0=v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-bool.pa; filename*1=tch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-bool.patch" Content-Transfer-Encoding: quoted-printable =46rom=20327358c08d803bf0c00f7fe897f8c2eb475b97a7=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Haibo=20Yan=20=0ADate:=20= Thu,=202=20Apr=202026=2021:22:54=20-0700=0ASubject:=20[PATCH=20v2]=20= jsonb:=20optimize=20object-field=20casts=20to=20numeric=20and=20bool=0A=0A= Add=20a=20support=20function=20for=20jsonb-to-numeric/bool=20casts=20= that=0Arecognizes=20casts=20over=20jsonb=20object-field=20extraction,=20= including=0Ajsonb_object_field(),=20->,=20and=20subscripting=20by=20key,=20= and=20rewrites=0Athem=20to=20explicit=20typed=20extractor=20calls.=0A=0A= This=20keeps=20ordinary=20SQL=20syntax=20unchanged=20while=20avoiding=20= extra=0Ajsonb=20scalar=20wrapping/unwrapping=20on=20the=20optimized=20= path.=0A=0AStage=201=20only:=20covers=20object-field=20extraction=20to=20= numeric/bool.=0A---=0A=20src/backend/utils/adt/jsonb.c=20=20=20=20=20=20=20= |=20126=20+++++++++++++++++=0A=20src/backend/utils/adt/jsonfuncs.c=20=20=20= |=20136=20++++++++++++++++++=0A=20src/include/catalog/pg_proc.dat=20=20=20= =20=20|=20=2018=20++-=0A=20src/test/regress/expected/jsonb.out=20|=20205=20= +++++++++++++++++++++++++++-=0A=20src/test/regress/sql/jsonb.sql=20=20=20= =20=20=20|=20=2063=20++++++++-=0A=205=20files=20changed,=20543=20= insertions(+),=205=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/utils/adt/jsonb.c=20b/src/backend/utils/adt/jsonb.c=0A= index=20864c5ac1c85..1ee15679733=20100644=0A---=20= a/src/backend/utils/adt/jsonb.c=0A+++=20b/src/backend/utils/adt/jsonb.c=0A= @@=20-17,6=20+17,9=20@@=0A=20#include=20"funcapi.h"=0A=20#include=20= "libpq/pqformat.h"=0A=20#include=20"miscadmin.h"=0A+#include=20= "nodes/makefuncs.h"=0A+#include=20"nodes/nodeFuncs.h"=0A+#include=20= "nodes/supportnodes.h"=0A=20#include=20"utils/builtins.h"=0A=20#include=20= "utils/fmgroids.h"=0A=20#include=20"utils/json.h"=0A@@=20-1816,6=20= +1819,129=20@@=20cannotCastJsonbValue(enum=20jbvType=20type,=20const=20= char=20*sqltype,=20Node=20*escontext)=0A=20=09return=20(Datum)=200;=0A=20= }=0A=20=0A+/*=0A+=20*=20jsonb_cast_support()=0A+=20*=0A+=20*=20Planner=20= support=20function=20for=20jsonb-to-scalar=20cast=20functions,=20= attached=20via=0A+=20*=20prosupport=20on=20the=20jsonb_numeric=20and=20= jsonb_bool=20catalog=20entries.=0A+=20*=0A+=20*=20When=20the=20sole=20= argument=20to=20the=20cast=20is=20a=20jsonb_object_field()=20call=20(the=20= ->=0A+=20*=20operator),=20we=20replace=20the=20two-step=20= cast(extract(...))=20expression=20with=20a=0A+=20*=20single=20typed=20= extractor=20that=20reads=20the=20scalar=20directly=20from=20the=20= in-memory=0A+=20*=20JsonbValue,=20avoiding=20a=20round-trip=20through=20= JsonbValueToJsonb.=0A+=20*=0A+=20*=20For=20example,=20(j=20->=20= 'a')::numeric=20is=20parsed=20as:=0A+=20*=20=20=20= jsonb_numeric(jsonb_object_field(j,=20'a'))=0A+=20*=20and=20is=20= rewritten=20to:=0A+=20*=20=20=20jsonb_object_field_numeric(j,=20'a')=0A+=20= */=0A+Datum=0A+jsonb_cast_support(PG_FUNCTION_ARGS)=0A+{=0A+=09Node=09=20= =20=20*rawreq=20=3D=20(Node=20*)=20PG_GETARG_POINTER(0);=0A+=09Node=09=20= =20=20*ret=20=3D=20NULL;=0A+=0A+=09if=20(IsA(rawreq,=20= SupportRequestSimplify))=0A+=09{=0A+=09=09SupportRequestSimplify=20*req=20= =3D=20(SupportRequestSimplify=20*)=20rawreq;=0A+=09=09FuncExpr=20=20=20= *fexpr=20=3D=20req->fcall;=0A+=09=09Node=09=20=20=20*arg;=0A+=09=09Oid=09= =09=09inner_funcid;=0A+=09=09List=09=20=20=20*inner_args;=0A+=09=09int=09= =09=09location;=0A+=09=09Oid=09=09=09replacement_funcid;=0A+=09=09Oid=09=09= =09replacement_rettype;=0A+=09=09FuncExpr=20=20=20*newfexpr;=0A+=0A+=09=09= /*=20The=20cast=20function=20must=20have=20exactly=20one=20argument=20*/=0A= +=09=09if=20(list_length(fexpr->args)=20!=3D=201)=0A+=09=09=09= PG_RETURN_POINTER(NULL);=0A+=0A+=09=09arg=20=3D=20(Node=20*)=20= linitial(fexpr->args);=0A+=0A+=09=09/*=0A+=09=09=20*=20Identify=20the=20= inner=20extraction=20expression.=20=20It=20may=20appear=20as=20a=0A+=09=09= =20*=20FuncExpr,=20an=20OpExpr,=20or=20a=20SubscriptingRef,=20depending=20= on=20how=20the=0A+=09=09=20*=20expression=20is=20represented=20at=20this=20= point.=20=20Accept=20the=20supported=0A+=09=09=20*=20forms.=0A+=09=09=20= */=0A+=09=09if=20(IsA(arg,=20FuncExpr))=0A+=09=09{=0A+=09=09=09FuncExpr=20= =20=20*inner=20=3D=20(FuncExpr=20*)=20arg;=0A+=0A+=09=09=09inner_funcid=20= =3D=20inner->funcid;=0A+=09=09=09inner_args=20=3D=20inner->args;=0A+=09=09= =09location=20=3D=20inner->location;=0A+=09=09}=0A+=09=09else=20if=20= (IsA(arg,=20OpExpr))=0A+=09=09{=0A+=09=09=09OpExpr=09=20=20=20*inner=20=3D= =20(OpExpr=20*)=20arg;=0A+=0A+=09=09=09inner_funcid=20=3D=20= inner->opfuncid;=0A+=09=09=09inner_args=20=3D=20inner->args;=0A+=09=09=09= location=20=3D=20inner->location;=0A+=09=09}=0A+=09=09else=20if=20= (IsA(arg,=20SubscriptingRef))=0A+=09=09{=0A+=09=09=09SubscriptingRef=20= *sbsref=20=3D=20(SubscriptingRef=20*)=20arg;=0A+=09=09=09Node=09=20=20=20= *subscript;=0A+=0A+=09=09=09/*=0A+=09=09=09=20*=20Only=20handle=20the=20= narrow=20case=20equivalent=20to=20object-field=0A+=09=09=09=20*=20= extraction:=20a=20single=20text-typed=20subscript=20on=20a=20jsonb=0A+=09= =09=09=20*=20container,=20with=20no=20slice=20and=20no=20assignment.=0A+=09= =09=09=20*/=0A+=09=09=09if=20(sbsref->refcontainertype=20!=3D=20= JSONBOID)=0A+=09=09=09=09PG_RETURN_POINTER(NULL);=0A+=09=09=09if=20= (list_length(sbsref->refupperindexpr)=20!=3D=201)=0A+=09=09=09=09= PG_RETURN_POINTER(NULL);=0A+=09=09=09if=20(sbsref->reflowerindexpr=20!=3D=20= NIL)=0A+=09=09=09=09PG_RETURN_POINTER(NULL);=0A+=09=09=09if=20= (sbsref->refassgnexpr=20!=3D=20NULL)=0A+=09=09=09=09= PG_RETURN_POINTER(NULL);=0A+=0A+=09=09=09subscript=20=3D=20(Node=20*)=20= linitial(sbsref->refupperindexpr);=0A+=09=09=09if=20(exprType(subscript)=20= !=3D=20TEXTOID)=0A+=09=09=09=09PG_RETURN_POINTER(NULL);=0A+=0A+=09=09=09= inner_funcid=20=3D=20F_JSONB_OBJECT_FIELD;=0A+=09=09=09inner_args=20=3D=20= list_make2(sbsref->refexpr,=20subscript);=0A+=09=09=09location=20=3D=20= exprLocation(arg);=0A+=09=09}=0A+=09=09else=0A+=09=09=09= PG_RETURN_POINTER(NULL);=0A+=0A+=09=09/*=20Only=20rewrite=20= jsonb_object_field(jsonb,=20text);=20verify=20arity=20too=20*/=0A+=09=09= if=20(inner_funcid=20!=3D=20F_JSONB_OBJECT_FIELD)=0A+=09=09=09= PG_RETURN_POINTER(NULL);=0A+=09=09if=20(list_length(inner_args)=20!=3D=20= 2)=0A+=09=09=09PG_RETURN_POINTER(NULL);=0A+=0A+=09=09/*=20Map=20the=20= outer=20cast=20to=20the=20corresponding=20typed=20extractor=20*/=0A+=09=09= if=20(fexpr->funcid=20=3D=3D=20F_NUMERIC_JSONB)=0A+=09=09{=0A+=09=09=09= replacement_funcid=20=3D=20F_JSONB_OBJECT_FIELD_NUMERIC;=0A+=09=09=09= replacement_rettype=20=3D=20NUMERICOID;=0A+=09=09}=0A+=09=09else=20if=20= (fexpr->funcid=20=3D=3D=20F_BOOL_JSONB)=0A+=09=09{=0A+=09=09=09= replacement_funcid=20=3D=20F_JSONB_OBJECT_FIELD_BOOL;=0A+=09=09=09= replacement_rettype=20=3D=20BOOLOID;=0A+=09=09}=0A+=09=09else=0A+=09=09=09= PG_RETURN_POINTER(NULL);=0A+=0A+=09=09/*=20Build=20the=20replacement=20= function=20call=20*/=0A+=09=09newfexpr=20=3D=20= makeFuncExpr(replacement_funcid,=20replacement_rettype,=0A+=09=09=09=09=09= =09=09=09inner_args,=20InvalidOid,=20InvalidOid,=0A+=09=09=09=09=09=09=09= =09COERCE_EXPLICIT_CALL);=0A+=09=09newfexpr->location=20=3D=20location;=0A= +=09=09ret=20=3D=20(Node=20*)=20newfexpr;=0A+=09}=0A+=0A+=09= PG_RETURN_POINTER(ret);=0A+}=0A+=0A=20Datum=0A=20= jsonb_bool(PG_FUNCTION_ARGS)=0A=20{=0Adiff=20--git=20= a/src/backend/utils/adt/jsonfuncs.c=20= b/src/backend/utils/adt/jsonfuncs.c=0Aindex=2097cc3d60340..fe11f5edd55=20= 100644=0A---=20a/src/backend/utils/adt/jsonfuncs.c=0A+++=20= b/src/backend/utils/adt/jsonfuncs.c=0A@@=20-921,6=20+921,142=20@@=20= jsonb_object_field_text(PG_FUNCTION_ARGS)=0A=20=09PG_RETURN_NULL();=0A=20= }=0A=20=0A+/*=0A+=20*=20Typed=20scalar=20extraction=20from=20jsonb=20= object=20fields.=0A+=20*=0A+=20*=20These=20functions=20extract=20a=20= typed=20scalar=20directly=20from=20the=20in-memory=0A+=20*=20JsonbValue=20= found=20by=20key=20lookup,=20skipping=20the=20intermediate=20Jsonb=0A+=20= *=20serialization=20that=20occurs=20with=20the=20unoptimized=20= cast-over-extraction=20path.=0A+=20*=0A+=20*=20They=20live=20here=20= alongside=20jsonb_object_field()=20because=20they=20share=20the=0A+=20*=20= same=20key-lookup=20logic=20(getKeyJsonValueFromContainer).=0A+=20*=0A+=20= *=20Registered=20in=20pg_proc=20as=20ordinary=20SQL-callable=20builtins;=20= also=20serve=20as=0A+=20*=20planner=20rewrite=20targets=20for=20= jsonb_cast_support()=20in=20jsonb.c.=0A+=20*=0A+=20*=20NULL=20semantics=20= match=20the=20existing=20cast=20path:=20a=20missing=20key=20or=20a=20= JSON=0A+=20*=20null=20value=20both=20produce=20SQL=20NULL;=20a=20type=20= mismatch=20raises=20ERROR.=0A+=20*/=0A+=0A+/*=0A+=20*=20Look=20up=20a=20= key=20in=20a=20jsonb=20object=20and=20return=20the=20JsonbValue,=20or=20= NULL.=0A+=20*=20Returns=20NULL=20(without=20error)=20when=20the=20input=20= is=20not=20an=20object,=20the=20key=0A+=20*=20is=20absent,=20or=20the=20= value=20is=20JSON=20null.=0A+=20*/=0A+static=20JsonbValue=20*=0A= +jsonb_object_field_lookup(Jsonb=20*jb,=20text=20*key,=20JsonbValue=20= *vbuf)=0A+{=0A+=09JsonbValue=20*v;=0A+=0A+=09if=20= (!JB_ROOT_IS_OBJECT(jb))=0A+=09=09return=20NULL;=0A+=0A+=09v=20=3D=20= getKeyJsonValueFromContainer(&jb->root,=0A+=09=09=09=09=09=09=09=09=09=20= VARDATA_ANY(key),=0A+=09=09=09=09=09=09=09=09=09=20= VARSIZE_ANY_EXHDR(key),=0A+=09=09=09=09=09=09=09=09=09=20vbuf);=0A+=0A+=09= /*=20Missing=20key=20or=20JSON=20null=20both=20map=20to=20SQL=20NULL=20= */=0A+=09if=20(v=20=3D=3D=20NULL=20||=20v->type=20=3D=3D=20jbvNull)=0A+=09= =09return=20NULL;=0A+=0A+=09return=20v;=0A+}=0A+=0A+/*=0A+=20*=20Raise=20= a=20type-mismatch=20error=20for=20typed=20field=20extraction.=0A+=20*=0A= +=20*=20The=20message=20wording=20matches=20cannotCastJsonbValue()=20in=20= jsonb.c=20so=20that=0A+=20*=20the=20optimized=20and=20unoptimized=20= paths=20produce=20identical=20errors.=0A+=20*/=0A+static=20void=0A= +jsonb_field_cast_error(JsonbValue=20*v,=20const=20char=20*sqltype)=0A+{=0A= +=09const=20char=20*jsontype;=0A+=0A+=09switch=20(v->type)=0A+=09{=0A+=09= =09case=20jbvNull:=0A+=09=09=09jsontype=20=3D=20"null";=0A+=09=09=09= break;=0A+=09=09case=20jbvString:=0A+=09=09=09jsontype=20=3D=20"string";=0A= +=09=09=09break;=0A+=09=09case=20jbvNumeric:=0A+=09=09=09jsontype=20=3D=20= "numeric";=0A+=09=09=09break;=0A+=09=09case=20jbvBool:=0A+=09=09=09= jsontype=20=3D=20"boolean";=0A+=09=09=09break;=0A+=09=09case=20jbvArray:=0A= +=09=09=09jsontype=20=3D=20"array";=0A+=09=09=09break;=0A+=09=09case=20= jbvObject:=0A+=09=09=09jsontype=20=3D=20"object";=0A+=09=09=09break;=0A+=09= =09case=20jbvBinary:=0A+=09=09=09jsontype=20=3D=20"array=20or=20object";=0A= +=09=09=09break;=0A+=09=09default:=0A+=09=09=09elog(ERROR,=20"unknown=20= jsonb=20type:=20%d",=20(int)=20v->type);=0A+=09=09=09jsontype=20=3D=20= NULL;=09/*=20keep=20compiler=20quiet=20*/=0A+=09}=0A+=0A+=09= ereport(ERROR,=0A+=09=09=09(errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=20errmsg("cannot=20cast=20jsonb=20%s=20to=20type=20%s",=0A+=09= =09=09=09=09jsontype,=20sqltype)));=0A+}=0A+=0A+/*=0A+=20*=20= jsonb_object_field_numeric=20-=20extract=20a=20numeric=20value=20from=20= a=20jsonb=20object=0A+=20*=20by=20field=20name.=20=20Returns=20NULL=20= for=20missing=20keys=20and=20JSON=20nulls.=0A+=20*/=0A+Datum=0A= +jsonb_object_field_numeric(PG_FUNCTION_ARGS)=0A+{=0A+=09Jsonb=09=20=20=20= *jb=20=3D=20PG_GETARG_JSONB_P(0);=0A+=09text=09=20=20=20*key=20=3D=20= PG_GETARG_TEXT_PP(1);=0A+=09JsonbValue=09vbuf;=0A+=09JsonbValue=20*v;=0A= +=09Numeric=09=09retValue;=0A+=0A+=09v=20=3D=20= jsonb_object_field_lookup(jb,=20key,=20&vbuf);=0A+=09if=20(v=20=3D=3D=20= NULL)=0A+=09=09PG_RETURN_NULL();=0A+=0A+=09if=20(v->type=20!=3D=20= jbvNumeric)=0A+=09=09jsonb_field_cast_error(v,=20"numeric");=0A+=0A+=09= retValue=20=3D=20DatumGetNumericCopy(NumericGetDatum(v->val.numeric));=0A= +=0A+=09PG_FREE_IF_COPY(jb,=200);=0A+=09PG_RETURN_NUMERIC(retValue);=0A= +}=0A+=0A+/*=0A+=20*=20jsonb_object_field_bool=20-=20extract=20a=20= boolean=20value=20from=20a=20jsonb=20object=0A+=20*=20by=20field=20name.=20= =20Returns=20NULL=20for=20missing=20keys=20and=20JSON=20nulls.=0A+=20*/=0A= +Datum=0A+jsonb_object_field_bool(PG_FUNCTION_ARGS)=0A+{=0A+=09Jsonb=09=20= =20=20*jb=20=3D=20PG_GETARG_JSONB_P(0);=0A+=09text=09=20=20=20*key=20=3D=20= PG_GETARG_TEXT_PP(1);=0A+=09JsonbValue=09vbuf;=0A+=09JsonbValue=20*v;=0A= +=0A+=09v=20=3D=20jsonb_object_field_lookup(jb,=20key,=20&vbuf);=0A+=09= if=20(v=20=3D=3D=20NULL)=0A+=09=09PG_RETURN_NULL();=0A+=0A+=09if=20= (v->type=20!=3D=20jbvBool)=0A+=09=09jsonb_field_cast_error(v,=20= "boolean");=0A+=0A+=09PG_FREE_IF_COPY(jb,=200);=0A+=09= PG_RETURN_BOOL(v->val.boolean);=0A+}=0A+=0A=20Datum=0A=20= json_array_element(PG_FUNCTION_ARGS)=0A=20{=0Adiff=20--git=20= a/src/include/catalog/pg_proc.dat=20b/src/include/catalog/pg_proc.dat=0A= index=203579cec5744..9a698ea3104=20100644=0A---=20= a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-4792,10=20+4792,10=20@@=0A=20=20= =20prosrc=20=3D>=20'numeric_pg_lsn'=20},=0A=20=0A=20{=20oid=20=3D>=20= '3556',=20descr=20=3D>=20'convert=20jsonb=20to=20boolean',=0A-=20=20= proname=20=3D>=20'bool',=20prorettype=20=3D>=20'bool',=20proargtypes=20= =3D>=20'jsonb',=0A+=20=20proname=20=3D>=20'bool',=20prosupport=20=3D>=20= 'jsonb_cast_support',=20prorettype=20=3D>=20'bool',=20proargtypes=20=3D>=20= 'jsonb',=0A=20=20=20prosrc=20=3D>=20'jsonb_bool'=20},=0A=20{=20oid=20=3D>=20= '3449',=20descr=20=3D>=20'convert=20jsonb=20to=20numeric',=0A-=20=20= proname=20=3D>=20'numeric',=20prorettype=20=3D>=20'numeric',=20= proargtypes=20=3D>=20'jsonb',=0A+=20=20proname=20=3D>=20'numeric',=20= prosupport=20=3D>=20'jsonb_cast_support',=20prorettype=20=3D>=20= 'numeric',=20proargtypes=20=3D>=20'jsonb',=0A=20=20=20prosrc=20=3D>=20= 'jsonb_numeric'=20},=0A=20{=20oid=20=3D>=20'3450',=20descr=20=3D>=20= 'convert=20jsonb=20to=20int2',=0A=20=20=20proname=20=3D>=20'int2',=20= prorettype=20=3D>=20'int2',=20proargtypes=20=3D>=20'jsonb',=0A@@=20= -12851,4=20+12851,18=20@@=0A=20=20=20proname=20=3D>=20= 'hashoid8extended',=20prorettype=20=3D>=20'int8',=0A=20=20=20proargtypes=20= =3D>=20'oid8=20int8',=20prosrc=20=3D>=20'hashoid8extended'=20},=0A=20=0A= +=0A+#=20jsonb=20cast=20optimization=20support=20functions=0A+{=20oid=20= =3D>=20'9950',=20descr=20=3D>=20'planner=20support=20for=20jsonb=20= casts',=0A+=20=20proname=20=3D>=20'jsonb_cast_support',=20prorettype=20= =3D>=20'internal',=0A+=20=20proargtypes=20=3D>=20'internal',=20prosrc=20= =3D>=20'jsonb_cast_support'=20},=0A+{=20oid=20=3D>=20'9951',=20descr=20= =3D>=20'extract=20numeric=20from=20jsonb=20object=20by=20field=20name',=0A= +=20=20proname=20=3D>=20'jsonb_object_field_numeric',=20prorettype=20=3D>=20= 'numeric',=0A+=20=20proargtypes=20=3D>=20'jsonb=20text',=20proargnames=20= =3D>=20'{from_json,field_name}',=0A+=20=20prosrc=20=3D>=20= 'jsonb_object_field_numeric'=20},=0A+{=20oid=20=3D>=20'9952',=20descr=20= =3D>=20'extract=20boolean=20from=20jsonb=20object=20by=20field=20name',=0A= +=20=20proname=20=3D>=20'jsonb_object_field_bool',=20prorettype=20=3D>=20= 'bool',=0A+=20=20proargtypes=20=3D>=20'jsonb=20text',=20proargnames=20=3D>= =20'{from_json,field_name}',=0A+=20=20prosrc=20=3D>=20= 'jsonb_object_field_bool'=20},=0A+=0A=20]=0Adiff=20--git=20= a/src/test/regress/expected/jsonb.out=20= b/src/test/regress/expected/jsonb.out=0Aindex=204e2467852db..11f9cccb649=20= 100644=0A---=20a/src/test/regress/expected/jsonb.out=0A+++=20= b/src/test/regress/expected/jsonb.out=0A@@=20-458,7=20+458,207=20@@=20= CREATE=20TEMP=20TABLE=20test_jsonb=20(=0A=20INSERT=20INTO=20test_jsonb=20= VALUES=0A=20('scalar','"a=20scalar"'),=0A=20('array','["zero",=20= "one","two",null,"four","five",=20[1,2,3],{"f1":9}]'),=0A= -('object','{"field1":"val1","field2":"val2","field3":null,=20"field4":=20= 4,=20"field5":=20[1,2,3],=20"field6":=20{"f1":9}}');=0A= +('object','{"field1":"val1","field2":"val2","field3":null,=20"field4":=20= 4,=20"field5":=20[1,2,3],=20"field6":=20{"f1":9},=20"field7":=20true}');=0A= +--=20Optimized=20typed=20extraction:=20the=20planner=20rewrites=20= (j->'key')::type=20into=20a=0A+--=20direct=20typed=20extractor=20call,=20= currently=20for=20numeric=20and=20bool=20only.=0A+--=20Section=201:=20= planner=20rewrite=20verification=20(rewritten=20targets)=0A+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field4')::numeric=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=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=20QUERY=20= PLAN=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=0A= +-----------------------------------------------------------------=0A+=20= Seq=20Scan=20on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20= jsonb_object_field_numeric(test_json,=20'field4'::text)=0A+=20=20=20= Filter:=20(test_jsonb.json_type=20=3D=20'object'::text)=0A+(3=20rows)=0A= +=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20= 'field7')::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=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=20QUERY=20PLAN=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=0A= +--------------------------------------------------------------=0A+=20= Seq=20Scan=20on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20= jsonb_object_field_bool(test_json,=20'field7'::text)=0A+=20=20=20Filter:=20= (test_jsonb.json_type=20=3D=20'object'::text)=0A+(3=20rows)=0A+=0A+--=20= Section=201b:=20planner=20rewrite=20verification=20for=20subscripting=20= syntax=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20= (test_json['field4'])::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20= =3D=20'object';=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=20QUERY=20PLAN=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=0A= +-----------------------------------------------------------------=0A+=20= Seq=20Scan=20on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20= jsonb_object_field_numeric(test_json,=20'field4'::text)=0A+=20=20=20= Filter:=20(test_jsonb.json_type=20=3D=20'object'::text)=0A+(3=20rows)=0A= +=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20= (test_json['field7'])::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=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=20QUERY=20PLAN=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=0A= +--------------------------------------------------------------=0A+=20= Seq=20Scan=20on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20= jsonb_object_field_bool(test_json,=20'field7'::text)=0A+=20=20=20Filter:=20= (test_jsonb.json_type=20=3D=20'object'::text)=0A+(3=20rows)=0A+=0A+--=20= Verify=20that=20unsupported=20cast=20targets=20are=20NOT=20rewritten=20= by=20this=20patch.=0A+--=20Integer=20and=20float=20casts=20remain=20on=20= the=20original=20jsonb_object_field=20+=20cast=20path.=0A+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field4')::int4=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +----------------------------------------------------=0A+=20Seq=20Scan=20= on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20((test_json=20->=20= 'field4'::text))::integer=0A+=20=20=20Filter:=20(test_jsonb.json_type=20= =3D=20'object'::text)=0A+(3=20rows)=0A+=0A+EXPLAIN=20(VERBOSE,=20COSTS=20= OFF)=20SELECT=20(test_json=20->=20'field4')::int8=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=0A+=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20QUERY=20PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=0A= +---------------------------------------------------=0A+=20Seq=20Scan=20= on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20((test_json=20->=20= 'field4'::text))::bigint=0A+=20=20=20Filter:=20(test_jsonb.json_type=20=3D= =20'object'::text)=0A+(3=20rows)=0A+=0A+EXPLAIN=20(VERBOSE,=20COSTS=20= OFF)=20SELECT=20(test_json=20->=20'field4')::float8=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=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=20QUERY=20PLAN=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=0A= +-------------------------------------------------------------=0A+=20Seq=20= Scan=20on=20pg_temp.test_jsonb=0A+=20=20=20Output:=20((test_json=20->=20= 'field4'::text))::double=20precision=0A+=20=20=20Filter:=20= (test_jsonb.json_type=20=3D=20'object'::text)=0A+(3=20rows)=0A+=0A+--=20= Section=202:=20correct=20execution=20through=20the=20rewritten=20path=0A= +SELECT=20(test_json=20->=20'field4')::numeric=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=0A+=20numeric=20=0A+---------=0A+=20=20= =20=20=20=20=204=0A+(1=20row)=0A+=0A+SELECT=20(test_json=20->=20= 'field7')::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=20bool=20=0A+------=0A+=20t=0A+(1=20row)=0A+=0A+--=20= Section=202b:=20correct=20execution=20through=20subscripting=20syntax=0A= +SELECT=20(test_json['field4'])::numeric=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=0A+=20test_json=20=0A+-----------=0A+=20=20=20= =20=20=20=20=20=204=0A+(1=20row)=0A+=0A+SELECT=20= (test_json['field7'])::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=20test_json=20=0A+-----------=0A+=20t=0A+(1=20row)=0A+=0A= +--=20Unsupported=20targets=20still=20work=20correctly=20through=20the=20= original=20path=0A+SELECT=20(test_json=20->=20'field4')::int4=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+=20int4=20=0A+------=0A= +=20=20=20=204=0A+(1=20row)=0A+=0A+SELECT=20(test_json=20->=20= 'field4')::float8=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=20float8=20=0A+--------=0A+=20=20=20=20=20=204=0A+(1=20= row)=0A+=0A+--=20Section=203:=20NULL=20semantics=20(missing=20key,=20= JSON=20null,=20non-object=20input)=0A+SELECT=20(test_json=20->=20= 'field3')::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=20=20--=20JSON=20null=0A+=20numeric=20=0A+---------=0A+=20=20=20= =20=20=20=20=20=0A+(1=20row)=0A+=0A+SELECT=20(test_json=20->=20= 'nonexistent')::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=20=20--=20missing=20key=0A+=20numeric=20=0A+---------=0A+=20=20= =20=20=20=20=20=20=0A+(1=20row)=0A+=0A+SELECT=20(test_json=20->=20= 'x')::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'array';=20= =20--=20non-object=0A+=20numeric=20=0A+---------=0A+=20=20=20=20=20=20=20= =20=0A+(1=20row)=0A+=0A+SELECT=20(test_json=20->=20'field3')::bool=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=20=20--=20JSON=20= null,=20bool=20path=0A+=20bool=20=0A+------=0A+=20=0A+(1=20row)=0A+=0A= +--=20Section=203b:=20NULL=20semantics=20through=20subscripting=20syntax=0A= +SELECT=20(test_json['field3'])::numeric=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=20=20--=20JSON=20null=0A+=20test_json=20=0A= +-----------=0A+=20=20=20=20=20=20=20=20=20=20=0A+(1=20row)=0A+=0A= +SELECT=20(test_json['nonexistent'])::numeric=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=20=20--=20missing=20key=0A+=20= test_json=20=0A+-----------=0A+=20=20=20=20=20=20=20=20=20=20=0A+(1=20= row)=0A+=0A+--=20Section=204:=20type-mismatch=20errors=20(scalar=20and=20= container=20types)=0A+SELECT=20(test_json=20->=20'field1')::numeric=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=20=20--=20string=20= to=20numeric=0A+ERROR:=20=20cannot=20cast=20jsonb=20string=20to=20type=20= numeric=0A+SELECT=20(test_json=20->=20'field1')::bool=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=20=20--=20string=20to=20= bool=0A+ERROR:=20=20cannot=20cast=20jsonb=20string=20to=20type=20boolean=0A= +SELECT=20(test_json=20->=20'field5')::numeric=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=20=20--=20array=20to=20numeric=0A= +ERROR:=20=20cannot=20cast=20jsonb=20array=20or=20object=20to=20type=20= numeric=0A+--=20Section=204b:=20type-mismatch=20error=20through=20= subscripting=20syntax=0A+SELECT=20(test_json['field1'])::numeric=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=20=20--=20string=20to=20= numeric=0A+ERROR:=20=20cannot=20cast=20jsonb=20string=20to=20type=20= numeric=0A+--=20Section=205:=20direct=20calls=20to=20typed=20extractor=20= builtins=0A+SELECT=20jsonb_object_field_numeric('{"a":=201}'::jsonb,=20= 'a');=0A+=20jsonb_object_field_numeric=20=0A= +----------------------------=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=201=0A+(1=20row)=0A+=0A+SELECT=20= jsonb_object_field_numeric('{"a":=203.14}'::jsonb,=20'a');=0A+=20= jsonb_object_field_numeric=20=0A+----------------------------=0A+=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=203.14=0A+(1=20= row)=0A+=0A+SELECT=20jsonb_object_field_bool('{"a":=20true}'::jsonb,=20= 'a');=0A+=20jsonb_object_field_bool=20=0A+-------------------------=0A+=20= t=0A+(1=20row)=0A+=0A+SELECT=20jsonb_object_field_bool('{"a":=20= false}'::jsonb,=20'a');=0A+=20jsonb_object_field_bool=20=0A= +-------------------------=0A+=20f=0A+(1=20row)=0A+=0A+--=20direct=20= calls:=20NULL=20semantics=0A+SELECT=20jsonb_object_field_numeric('{"a":=20= 1}'::jsonb,=20'missing');=0A+=20jsonb_object_field_numeric=20=0A= +----------------------------=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=0A+(1=20row)=0A+=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20null}'::jsonb,=20'a');=0A+=20= jsonb_object_field_numeric=20=0A+----------------------------=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=0A= +(1=20row)=0A+=0A+SELECT=20jsonb_object_field_bool('{"a":=20= true}'::jsonb,=20'missing');=0A+=20jsonb_object_field_bool=20=0A= +-------------------------=0A+=20=0A+(1=20row)=0A+=0A+--=20direct=20= calls:=20type-mismatch=20errors=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20"text"}'::jsonb,=20'a');=0A+ERROR:=20= =20cannot=20cast=20jsonb=20string=20to=20type=20numeric=0A+SELECT=20= jsonb_object_field_bool('{"a":=201}'::jsonb,=20'a');=0A+ERROR:=20=20= cannot=20cast=20jsonb=20numeric=20to=20type=20boolean=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20{"x":1}}'::jsonb,=20'a');=20=20--=20= container=20to=20scalar=0A+ERROR:=20=20cannot=20cast=20jsonb=20array=20= or=20object=20to=20type=20numeric=0A=20SELECT=20test_json=20->=20'x'=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'scalar';=0A=20=20?column?=20= =0A=20----------=0A@@=20-586,7=20+786,8=20@@=20SELECT=20= jsonb_object_keys(test_json)=20FROM=20test_jsonb=20WHERE=20json_type=20=3D= =20'object';=0A=20=20field4=0A=20=20field5=0A=20=20field6=0A-(6=20rows)=0A= +=20field7=0A+(7=20rows)=0A=20=0A=20--=20nulls=0A=20SELECT=20= (test_json->'field3')=20IS=20NULL=20AS=20expect_false=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=0Adiff=20--git=20= a/src/test/regress/sql/jsonb.sql=20b/src/test/regress/sql/jsonb.sql=0A= index=20d28ed1c1e85..09836bca0a9=20100644=0A---=20= a/src/test/regress/sql/jsonb.sql=0A+++=20= b/src/test/regress/sql/jsonb.sql=0A@@=20-155,7=20+155,68=20@@=20CREATE=20= TEMP=20TABLE=20test_jsonb=20(=0A=20INSERT=20INTO=20test_jsonb=20VALUES=0A= =20('scalar','"a=20scalar"'),=0A=20('array','["zero",=20= "one","two",null,"four","five",=20[1,2,3],{"f1":9}]'),=0A= -('object','{"field1":"val1","field2":"val2","field3":null,=20"field4":=20= 4,=20"field5":=20[1,2,3],=20"field6":=20{"f1":9}}');=0A= +('object','{"field1":"val1","field2":"val2","field3":null,=20"field4":=20= 4,=20"field5":=20[1,2,3],=20"field6":=20{"f1":9},=20"field7":=20true}');=0A= +=0A+--=20Optimized=20typed=20extraction:=20the=20planner=20rewrites=20= (j->'key')::type=20into=20a=0A+--=20direct=20typed=20extractor=20call,=20= currently=20for=20numeric=20and=20bool=20only.=0A+=0A+--=20Section=201:=20= planner=20rewrite=20verification=20(rewritten=20targets)=0A+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field4')::numeric=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field7')::bool=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+=0A+--=20= Section=201b:=20planner=20rewrite=20verification=20for=20subscripting=20= syntax=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20= (test_json['field4'])::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20= =3D=20'object';=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20= (test_json['field7'])::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=0A+--=20Verify=20that=20unsupported=20cast=20targets=20are=20= NOT=20rewritten=20by=20this=20patch.=0A+--=20Integer=20and=20float=20= casts=20remain=20on=20the=20original=20jsonb_object_field=20+=20cast=20= path.=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20= 'field4')::int4=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20= ->=20'field4')::int8=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+EXPLAIN=20(VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20= ->=20'field4')::float8=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=0A+--=20Section=202:=20correct=20execution=20through=20the=20= rewritten=20path=0A+SELECT=20(test_json=20->=20'field4')::numeric=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+SELECT=20(test_json=20= ->=20'field7')::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=0A+--=20Section=202b:=20correct=20execution=20through=20= subscripting=20syntax=0A+SELECT=20(test_json['field4'])::numeric=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+SELECT=20= (test_json['field7'])::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+=0A+--=20Unsupported=20targets=20still=20work=20correctly=20= through=20the=20original=20path=0A+SELECT=20(test_json=20->=20= 'field4')::int4=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=0A+SELECT=20(test_json=20->=20'field4')::float8=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+=0A+--=20Section=20= 3:=20NULL=20semantics=20(missing=20key,=20JSON=20null,=20non-object=20= input)=0A+SELECT=20(test_json=20->=20'field3')::numeric=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'object';=20=20--=20JSON=20null=0A= +SELECT=20(test_json=20->=20'nonexistent')::numeric=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=20=20--=20missing=20key=0A+SELECT=20= (test_json=20->=20'x')::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20= =3D=20'array';=20=20--=20non-object=0A+SELECT=20(test_json=20->=20= 'field3')::bool=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20= 'object';=20=20--=20JSON=20null,=20bool=20path=0A+=0A+--=20Section=203b:=20= NULL=20semantics=20through=20subscripting=20syntax=0A+SELECT=20= (test_json['field3'])::numeric=20FROM=20test_jsonb=20WHERE=20json_type=20= =3D=20'object';=20=20--=20JSON=20null=0A+SELECT=20= (test_json['nonexistent'])::numeric=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=20=20--=20missing=20key=0A+=0A+--=20Section=20= 4:=20type-mismatch=20errors=20(scalar=20and=20container=20types)=0A= +SELECT=20(test_json=20->=20'field1')::numeric=20FROM=20test_jsonb=20= WHERE=20json_type=20=3D=20'object';=20=20--=20string=20to=20numeric=0A= +SELECT=20(test_json=20->=20'field1')::bool=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=20=20--=20string=20to=20bool=0A+SELECT=20= (test_json=20->=20'field5')::numeric=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=20=20--=20array=20to=20numeric=0A+=0A+--=20= Section=204b:=20type-mismatch=20error=20through=20subscripting=20syntax=0A= +SELECT=20(test_json['field1'])::numeric=20FROM=20test_jsonb=20WHERE=20= json_type=20=3D=20'object';=20=20--=20string=20to=20numeric=0A+=0A+--=20= Section=205:=20direct=20calls=20to=20typed=20extractor=20builtins=0A= +SELECT=20jsonb_object_field_numeric('{"a":=201}'::jsonb,=20'a');=0A= +SELECT=20jsonb_object_field_numeric('{"a":=203.14}'::jsonb,=20'a');=0A= +SELECT=20jsonb_object_field_bool('{"a":=20true}'::jsonb,=20'a');=0A= +SELECT=20jsonb_object_field_bool('{"a":=20false}'::jsonb,=20'a');=0A+--=20= direct=20calls:=20NULL=20semantics=0A+SELECT=20= jsonb_object_field_numeric('{"a":=201}'::jsonb,=20'missing');=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20null}'::jsonb,=20'a');=0A+SELECT=20= jsonb_object_field_bool('{"a":=20true}'::jsonb,=20'missing');=0A+--=20= direct=20calls:=20type-mismatch=20errors=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20"text"}'::jsonb,=20'a');=0A+SELECT=20= jsonb_object_field_bool('{"a":=201}'::jsonb,=20'a');=0A+SELECT=20= jsonb_object_field_numeric('{"a":=20{"x":1}}'::jsonb,=20'a');=20=20--=20= container=20to=20scalar=0A=20=0A=20SELECT=20test_json=20->=20'x'=20FROM=20= test_jsonb=20WHERE=20json_type=20=3D=20'scalar';=0A=20SELECT=20test_json=20= ->=20'x'=20FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'array';=0A--=20= =0A2.52.0=0A=0A= --Apple-Mail=_83434D77-760B-42C1-A4EB-0BCAFD7A1640 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii

--Apple-Mail=_83434D77-760B-42C1-A4EB-0BCAFD7A1640-- --Apple-Mail=_09156B8A-76C8-4F65-931E-87B8D096919B--