On Tue, Aug 1, 2023 at 12:39 PM Andy Fan <zhihui.fan1...@gmail.com> wrote: > > Hi: > > Currently if we want to extract a numeric field in jsonb, we need to use > the following expression: cast (a->>'a' as numeric). It will turn a numeric > to text first and then turn the text to numeric again. See > jsonb_object_field_text and JsonbValueAsText. However the binary format > of numeric in JSONB is compatible with the numeric in SQL, so I think we > can have an operator to extract the numeric directly. If the value of a given > field is not a numeric data type, an error will be raised, this can be > documented. > > In this patch, I added a new operator for this purpose, here is the > performance gain because of this. > > create table tb (a jsonb); > insert into tb select '{"a": 1}'::jsonb from generate_series(1, 100000)i; > > current method: > select count(*) from tb where cast (a->>'a' as numeric) = 2; > 167ms. > > new method: > select count(*) from tb where a@->'a' = 2; > 65ms. > > Is this the right way to go? Testcase, document and catalog version are > updated. > > > -- > Best Regards > Andy Fan
return PointerGetDatum(v->val.numeric); should be something like PG_RETURN_NUMERIC(v->val.numeric); ?