Re: Extract numeric filed in JSONB more effectively

On Mon, Apr 6, 2026 at 12:55 PM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:

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

Since this is my first patch submission, I forgot to include the original
author.
Thanks to Andy Fan for the suggestion — I’ve now added him as a co-author.
Regards
Haibo