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
v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch
Description: Binary data
