public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Thu, 3 Aug 2023 15:53:47 +0800
Message-ID: <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
<CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
Hi Pavel:
Thanks for the feedback.
I don't like this solution because it is bloating operators and it is not
> extra readable.
>
If we support it with cast, could we say we are bloating CAST? It is true
that it is not extra readable, if so how about a->>'a' return text?
Actually
I can't guess any meaning of the existing jsonb operations without
documentation.
For completeness you should implement cast for date, int, boolean too.
> Next, the same problem is with XML or hstore type (probably with any types
> that are containers).
>
I am not sure completeness is a gold rule we should obey anytime,
like we have some function like int24le to avoid the unnecessary
cast, but we just avoid casting for special types for performance
reason, but not for all. At the same time, `int2/int4/int8` doesn't
have a binary compatibility type in jsonb. and the serialization
/deserialization for boolean is pretty cheap.
I didn't realize timetime types are binary compatible with SQL,
so maybe we can have some similar optimization as well.
(It is a pity that timestamp(tz) are not binary, or else we may
just need one operator).
>
> I don't like the idea so using a special operator is 2x faster than common
> syntax for casting. It is a signal, so there is a space for optimization.
> Black magic with special operators is not user friendly for relatively
> common problems.
>
I don't think "Black magic" is a proper word here, since it is not much
different from ->> return a text. If you argue text can be cast to
most-of-types, that would be a reason, but I doubt this difference
should generate a "black magic".
>
> Maybe we can introduce some *internal operator* "extract to type", and in
> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
> 'field', typid)
>
Not sure what the OP should be? If it is a function, what is the
return value? It looks to me like it is hard to do in c language?
After all, if we really care about the number of operators, I'm OK
with just let users use the function directly, like
jsonb_field_as_numeric(jsonb, 'filedname')
jsonb_field_as_timestamp(jsonb, 'filedname');
jsonb_field_as_timestamptz(jsonb, 'filedname');
jsonb_field_as_date(jsonb, 'filedname');
it can save an operator and sloves the readable issue.
--
Best Regards
Andy Fan
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox