Extract numeric filed in JSONB more effectively
Andy Fan <zhihui.fan1213@gmail.com>
From: Andy Fan <zhihui.fan1213@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-08-01T04:38:57Z
Lists: pgsql-hackers
Attachments
- v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch (application/octet-stream) patch v1-0001
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
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add trailing commas to enum definitions
- 611806cd726f 17.0 cited