Re: Extract numeric filed in JSONB more effectively

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

On Apr 6, 2026, at 6:05 AM, 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 = (FuncExpr *) arg;
+
+ inner_funcid = inner->funcid;
+ inner_args = inner->args;
+ location = inner->location;
+ }
+ else if (IsA(arg, OpExpr))
+ {
+ OpExpr   *inner = (OpExpr *) arg;
+
+ inner_funcid = inner->opfuncid;
+ inner_args = inner->args;
+ location = 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 = '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