Re: Extract numeric filed in JSONB more effectively

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

>
>
> 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
>

Sorry — the previous version failed to build because of a duplicate objid.
I’ve reuploaded a fixed version here.

Regards,
Haibo