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 1w8WQZ-000cRx-2i for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Apr 2026 04:46:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w8WQX-009iMB-1W for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Apr 2026 04:46:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w8WQW-009iM1-2z for pgsql-hackers@lists.postgresql.org; Fri, 03 Apr 2026 04:46:37 +0000 Received: from mail-dl1-x122a.google.com ([2607:f8b0:4864:20::122a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w8WQU-00000000Ic8-3qk3 for pgsql-hackers@lists.postgresql.org; Fri, 03 Apr 2026 04:46:36 +0000 Received: by mail-dl1-x122a.google.com with SMTP id a92af1059eb24-12776bebe9fso3902835c88.1 for ; Thu, 02 Apr 2026 21:46:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775191594; x=1775796394; 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=R7ep6bC9kNFCjAsnhsQXzRz6YULVtvxr7m3IXVsBhFM=; b=Ei1X6Qw0BeL/UN6rANiEPpFn46zd8/xGvta++YEQuzuFkHGS9uCoxoPJ2QcO1bOtk8 1kkjO9+YpLLUiKyja3iXh0aSYWPzUt5FiSgUqCaXxXhdayz8A6tYnbPblNUIyQH3smch avcDlx5LnV4tAAT1p7RZN7AM1w47ueWndEaLD14u6irPVxxQxDvhPh62qr4/A/3HbIOX isYazfmNF5h6xXkLaLLeHVgoN5a8qB0gqq0mQnueHe9EY9S+Pr/FuzYOOe98yzYQqdb2 fhi7GsAEGpwEtO9U2mYFZ/b0RC7bU0WStPfr3KWhHIj9ZgHivmdtQq8hIlf8M0hklKR5 NyLw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775191594; x=1775796394; 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=R7ep6bC9kNFCjAsnhsQXzRz6YULVtvxr7m3IXVsBhFM=; b=JhS9wwca/U3lhaZW4ij1cBbAMKXqpavMjNTBb8p1PTs1atFyFuWpOu6KRvqfuETvSw 12ANXHCbw3sWSsOcbEyPFw/UKubQxV3aVTJ+Hibj+Kwmr+uVwN2v9hwdaPkOR3NAqrRj YIgAm4KsnY8dk+uql7sCBNJDOhXZh49P+dICRw78fONT2oumEWqEit1t2UyMhWT2Rntm vcZ6Fe5TlgG+gFGJTkz1DVhS5Z7oxLf1kjyel2Jnx0ZDXIo3wuLM8Q0lEbylIf485xWJ nKY2ySQw7BayxreznEhPEx4K89fet1rNJ/dFLf4lYfSE6Drymc9f1CsY0JYGHCh1+S8j NsZg== X-Forwarded-Encrypted: i=1; AJvYcCXar3n2zZu2zIqxy/VMvsI/7not7ncEMif7QBQjqAvCRxrk3ZM5/u9/vqzFRaBQUECoe0e/549egyQqu9HK@lists.postgresql.org X-Gm-Message-State: AOJu0YxVzrs7KzXP9iTClvFcV6VdxwwIwiZjeToIxSvcbiAn4P5dj44Q ov8WZHdtM/Hoc3o+FbJbivNZctjYyNCUobLXOcELStnyhx9j3GyjcY/f X-Gm-Gg: ATEYQzx+MwQbo0r/Pcu3qWr0yBWjwomIPEozXbIVlAMM17yA/sR1g3zSPq1JKsRAwTz t/+fWwZnavy7iPY58JksJeOuigq3dFezS4av8TREo8pvy+wu/yWsVhP1zV1JizTTwvjrai3CTZa U5kyqp8YjaLBKggwSYWGBXksbMPVx51MM/OvN61z0RY6y3oUHT8tH9TsVTQak/3kruZzUBkIYUq PHENFMl5EBCcIoVKtp7y+q51+1MEM8M4OENzgXCrxmt3sIt7WzGDrRDQ7NnGDIo7IcatCWwG8Tl qFMDCTMT18oH/CLeVuMBYuCFCQgJDSh+RX6ycv8ZEiYTHdsEHoFm8Ee3hR2tgiSQQUMBdictmCE cNJt6a5zrhTbPL7xCK0jmX6UN6Y/RpaS7wQ7PGR2bk/CsObHdlx/xaBjKXVQ/cUZLgKeEbIy0Tz 1RBaVvlTLis2BWnOHt2kZ1HgVLYFT0aqsQa2sCGQpKS+Ng0/wo3dmqPgkjqCgEHg== X-Received: by 2002:a05:7022:52b:b0:128:d34a:320f with SMTP id a92af1059eb24-12bfb6fdc70mr922507c88.12.1775191593891; Thu, 02 Apr 2026 21:46:33 -0700 (PDT) Received: from smtpclient.apple ([2620:149:13d0::ccf]) by smtp.gmail.com with ESMTPSA id a92af1059eb24-12bedd8ff27sm3712025c88.7.2026.04.02.21.46.32 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Apr 2026 21:46:33 -0700 (PDT) From: Haibo Yan X-Google-Original-From: Haibo Yan Message-Id: <7487A577-7BDC-4B94-9FFA-741E95439BFC@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_7E2C4C91-BDA5-48E1-8D83-03A4F18E01D5" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.500.181\)) Subject: Re: Extract numeric filed in JSONB more effectively Date: Thu, 2 Apr 2026 21:46:22 -0700 In-Reply-To: Cc: Andy Fan , David Rowley , Peter Eisentraut , Amit Langote , Alvaro Herrera , jian he , Chapman Flack , pgsql-hackers@lists.postgresql.org To: Dmitry Dolgov <9erthalion6@gmail.com> References: <87r0hmvuvr.fsf@163.com> <8102ff5b-b156-409e-a48f-e53e63a39b36@eisentraut.org> <8734t6c5rh.fsf@163.com> <87o7bn7z56.fsf@163.com> <875xx197bp.fsf@163.com> <87ttk0lgcx.fsf@163.com> <8734m5fua1.fsf@163.com> <87a5dx4cfb.fsf@163.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=_7E2C4C91-BDA5-48E1-8D83-03A4F18E01D5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Nov 22, 2024, at 10:14=E2=80=AFAM, Dmitry Dolgov = <9erthalion6@gmail.com> wrote: >=20 >> On Mon, Nov 18, 2024 at 08:23:52AM GMT, Andy Fan wrote: >>=20 >>>>> I imagined you'd the patch should create a SupportRequestSimplify >>>>> support function for jsonb_numeric() that checks if the input >>>>> expression is an OpExpr with funcid of jsonb_object_field(). All = you >>>>> do then is ditch the cast and change the OpExpr to call a new = function >>>>> named jsonb_object_field_numeric() which returns the val.numeric >>>>> directly. Likely the same support function could handle jsonb = casts >>>>> to other types too, in which case you'd just call some other = function, >>>>> e.g jsonb_object_field_timestamp() or = jsonb_object_field_boolean(). >>>>=20 >>>> Basically yes. The reason complexity comes when we many operators = we >>>> want to optimize AND my patch I want to reduce the number of = function >>>> created. >>>>=20 >>>> [...] >>>>=20 >>>> Within the start / finish function, we need to create *7* = functions. >>>=20 >>> Any particular reason you want to keep number of functions minimal? = Is >>> it just to make the patch smaller? I might be missing something = without >>> looking at the implementation in details, but the difference between = 10 >>> and 7 functions doesn't seem to be significant. >>=20 >> Another reason is for reducing code duplication, writting too many >> similar function looks not good to me. Chapman expressed this idea >> first at [1]. Search "it would make me happy to further reduce some >> of the code" in the message. >>=20 >> Acutally this doesn't make the patch complexer too much. >>=20 >> [1] >> = https://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40a= nastigmatix.net >=20 > It might not make everything too much complex, but e.g. relabeling of > the first argument for a "finish" function into an internal one sounds > strange to me. Maybe there is a way to avoid duplication of the code, > but keep all needed functions in pg_proc? >=20 > Btw, sorry to complain about small details, but I find start / finish > naming pattern not quite fitting here. Their main purpose is to = extract > / convert a value, the order in which they are happening is less > relevant. >=20 >=20 >=20 >=20 Hi all, I=E2=80=99d like to continue pushing this patch forward. Based on the earlier discussion, I reworked the patch into a smaller = stage-1 version with a narrower scope and a simpler rewrite strategy. = The current patch keeps the normal SQL syntax unchanged and uses = support-function simplification to rewrite only the following patterns: (jsonb_object_field(...))::numeric jsonb_object_field(...))::bool into explicit typed extractor calls. So at this stage it intentionally covers only: jsonb_object_field(...) / -> casts to numeric casts to bool and does not yet try to cover array/path extraction or integer/float = typed extractors. I also ran a small microbenchmark to isolate the cast-over-object-field = path. On my setup, the current patch shows the following gains: Query Before After = Speedup = --------------------------------------------------------------------------= ------ SELECT sum((j->'n')::numeric) FROM t 118.028 ms 56.082 ms = 2.10x SELECT count(*) FROM t WHERE (j->'b')::bool 115.665 ms 51.945 ms = 2.23x = --------------------------------------------------------------------------= ------ These are microbenchmark numbers rather than end-to-end workload = results, but they suggest that the simplified rewrite path is worth = pursuing. My goal with this version is not to solve the full matrix at once, but = to first land a reviewer-friendly subset that: 1. does not introduce new user-visible operators, 2. keeps ordinary cast syntax unchanged, 3. avoids the more abstract internal/start-finish style machinery, 4. and uses explicit rewrite targets that are easier to review. If this direction looks reasonable, I=E2=80=99d appreciate another round = of review on the updated patch. If people think the stage-1 scope is = acceptable, I can continue with follow-up patches for additional = extractor families and target types. Thanks, Haibo --Apple-Mail=_7E2C4C91-BDA5-48E1-8D83-03A4F18E01D5 Content-Disposition: attachment; filename=0001-jsonb-optimize-object-field-casts-to-numeric-and-boo.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0001-jsonb-optimize-object-field-casts-to-numeric-and-boo.patch" Content-Transfer-Encoding: quoted-printable =46rom=20077780e4e91b3cb4a4f37a298615f01f5e4122c2=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]=20jsonb:=20= optimize=20object-field=20casts=20to=20numeric=20and=20bool=0A=0AAdd=20a=20= support=20function=20for=20jsonb-to-numeric/bool=20casts=20that=0A= recognizes=20casts=20over=20jsonb_object_field()/->=20expressions=20and=0A= rewrites=20them=20to=20explicit=20typed=20extractor=20calls.=0A=0AThis=20= keeps=20ordinary=20SQL=20syntax=20unchanged=20while=20avoiding=20extra=0A= jsonb=20scalar=20wrapping/unwrapping=20on=20the=20optimized=20path.=0A=0A= Stage=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= |=20=2096=20+++++++++++++++++=0A=20src/backend/utils/adt/jsonfuncs.c=20=20= =20|=20136=20++++++++++++++++++++++++=0A=20= src/include/catalog/pg_proc.dat=20=20=20=20=20|=20=2018=20+++-=0A=20= src/test/regress/expected/jsonb.out=20|=20159=20= +++++++++++++++++++++++++++-=0A=20src/test/regress/sql/jsonb.sql=20=20=20= =20=20=20|=20=2048=20++++++++-=0A=205=20files=20changed,=20452=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..fccbb2fbab6=20100644=0A---=20= a/src/backend/utils/adt/jsonb.c=0A+++=20b/src/backend/utils/adt/jsonb.c=0A= @@=20-17,6=20+17,8=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/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+1818,100=20@@=20= cannotCastJsonbValue(enum=20jbvType=20type,=20const=20char=20*sqltype,=20= Node=20*escontext)=0A=20=09return=20(Datum)=200;=0A=20}=0A=20=0A+/*=0A+=20= *=20jsonb_cast_support()=0A+=20*=0A+=20*=20Planner=20support=20function=20= for=20jsonb-to-scalar=20cast=20functions,=20attached=20via=0A+=20*=20= prosupport=20on=20the=20jsonb_numeric=20and=20jsonb_bool=20catalog=20= entries.=0A+=20*=0A+=20*=20When=20the=20sole=20argument=20to=20the=20= cast=20is=20a=20jsonb_object_field()=20call=20(the=20->=0A+=20*=20= operator),=20we=20replace=20the=20two-step=20cast(extract(...))=20= expression=20with=20a=0A+=20*=20single=20typed=20extractor=20that=20= reads=20the=20scalar=20directly=20from=20the=20in-memory=0A+=20*=20= JsonbValue,=20avoiding=20a=20round-trip=20through=20JsonbValueToJsonb.=0A= +=20*=0A+=20*=20For=20example,=20(j=20->=20'a')::numeric=20is=20parsed=20= as:=0A+=20*=20=20=20jsonb_numeric(jsonb_object_field(j,=20'a'))=0A+=20*=20= and=20is=20rewritten=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+=09= Node=09=20=20=20*rawreq=20=3D=20(Node=20*)=20PG_GETARG_POINTER(0);=0A+=09= Node=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=0A+=09=09=20= *=20either=20a=20FuncExpr=20or=20an=20OpExpr;=20accept=20both=20forms.=0A= +=09=09=20*/=0A+=09=09if=20(IsA(arg,=20FuncExpr))=0A+=09=09{=0A+=09=09=09= FuncExpr=20=20=20*inner=20=3D=20(FuncExpr=20*)=20arg;=0A+=0A+=09=09=09= inner_funcid=20=3D=20inner->funcid;=0A+=09=09=09inner_args=20=3D=20= inner->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=20inner->opfuncid;=0A+=09=09=09inner_args=20=3D=20inner->args;=0A+=09= =09=09location=20=3D=20inner->location;=0A+=09=09}=0A+=09=09else=0A+=09=09= =09PG_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..81091ea9038=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,161=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= 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= Unsupported=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=204:=20type-mismatch=20errors=20(scalar=20and=20container=20= types)=0A+SELECT=20(test_json=20->=20'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+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=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+740,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..5cb16b7fe2b=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,53=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= 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+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field4')::int8=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=0A+EXPLAIN=20= (VERBOSE,=20COSTS=20OFF)=20SELECT=20(test_json=20->=20'field4')::float8=20= FROM=20test_jsonb=20WHERE=20json_type=20=3D=20'object';=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+SELECT=20(test_json=20->=20= '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=204:=20= type-mismatch=20errors=20(scalar=20and=20container=20types)=0A+SELECT=20= (test_json=20->=20'field1')::numeric=20FROM=20test_jsonb=20WHERE=20= json_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=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=_7E2C4C91-BDA5-48E1-8D83-03A4F18E01D5--