Re: Extract numeric filed in JSONB more effectively

On Mon, Apr 6, 2026 at 10:57 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:

>
>
> On Apr 6, 2026, at 10:51 AM, Haibo Yan <Tristan(dot)Yim(at)gmail(dot)com> wrote:
>
>
> On Apr 6, 2026, at 6:05 AM, Dmitry Dolgov <9erthalion6(at)gmail(dot)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
>
> It looks like the attachment may have been dropped in transit. I’m
> reattaching it here.
>
> Regards,
>
> Haibo
>
>
> This is quite embarrassing — it looks like my attachment got lost
somewhere in transit. I’m not sure what happened. I did confirm locally
that the attachment was included, but I can’t see it in the mail thread.
I’m now trying to resend it using a different mail client. Hopefully this
won’t cause too much disruption for everyone.

Haibo