Re: Extract numeric filed in JSONB more effectively

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