public inbox for [email protected]  

help / color / mirror / Atom feed

From: 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 21:22:48 +0800
Message-ID: <CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@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>
	<CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
	<CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>

Hi:


> More, I believe so lot of people uses more common syntax, and then this
> syntax should to have good performance - for jsonb - (val->'op')::numeric
> works, and then there should not be performance penalty, because this
> syntax will be used in 99%.
>

This looks like a valid opinion IMO,  but to rescue it, we have to do
something like "internal structure" and remove the existing cast.
But even we pay the effort, it still breaks some common knowledge,
since xx:numeric is not a cast.  It is an "internal structure"!


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".
>>
>
> I used the term black magic, because nobody without reading documentation
> can find this operator.
>

I think this is what document is used for..


> It is used just for this special case, and the functionality is the same
> as using cast (only with different performance).
>

This is not good, but I didn't see a better choice so far,  see my first
graph.


>
> The operator ->> is more widely used. But if we have some possibility to
> work without it, then the usage for a lot of users will be more simple.
> More if the target types can be based on context
>

It would be cool but still I didn't see a way to do that without making
something else complex.


>>> 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?
>>
>
> It should be internal structure - it can be similar like COALESCE or IS
> operator
>

It may work, but see my answer in the first graph.


>
>
>>
>> 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.
>>
>
> I don't like it too much, but it is better than introduction new operator
>

Good to know it.  Naming operators is a complex task  if we add four.


> We already have the jsonb_extract_path and jsonb_extract_path_text
> function.
>

I can't follow this.  jsonb_extract_path returns a jsonb, which is  far
away from
our goal: return a numeric effectively?

I can imagine to usage "anyelement" type too. some like
> `jsonb_extract_path_type(jsonb, anyelement, variadic text[] )`
>

Can you elaborate this please?

-- 
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: <CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@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