Re: Extract numeric filed in JSONB more effectively

út 15. 8. 2023 v 8:04 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> napsal:

>
>> My idea of an ideal solution is the introduction of the possibility to
>> use "any" pseudotype as return type with possibility to set default return
>> type. Now, "any" is allowed only for arguments. The planner can set the
>> expected type when it knows it, or can use the default type.
>>
>> so for extraction of jsonb field we can use FUNCTION
>> jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
>>
>
>
Is this an existing framework or do you want to create something new?
>

This should be created

>
>> if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
>> if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date
>>
>
> If so, what is the difference from the current jsonb->'f' and
> (jsonb->'f' )::date?
>

a) effectiveness. The ending performance should be similar like your
current patch, but without necessity to use planner support API.

b) more generic usage. For example, the expressions in plpgsql are executed
a little bit differently than SQL queries. So there the optimization from
your patch probably should not work, because you can write only var :=
j->'f', and plpgsql forces cast function execution, but not via planner.

c) nothing else. It should not to require to modify cast function
definitions

>> With this possibility we don't need to touch to cast functions, and we
>> can simply implement similar functions for other non atomic types.
>>
>
> What do you mean by "atomic type" here? If you want to introduce some
> new framework, I think we need a very clear benefit.
>

Atomic types (skalar types like int, varchar, date), nonatomic types -
array, composite, xml, jsonb, hstore or arrays of composite types.

>
> --
> Best Regards
> Andy Fan
>