public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Extract numeric filed in JSONB more effectively
Date: Tue, 1 Aug 2023 12:38:57 +0800
Message-ID: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com> (raw)
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
Attachments:
[application/octet-stream] v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch (5.7K, 3-v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch)
download | inline diff:
From 1415e09d6ca860d466939229e6d6a9012bf2bbcf Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Tue, 1 Aug 2023 10:38:29 +0800
Subject: [PATCH v1] Add jsonb operator to return a numeric directly.
The binary format of numeric in JOSNB is compatible with the numeric
in SQL, so we can get the numeric more effectively.
---
doc/src/sgml/func.sgml | 14 +++++++++++
src/backend/utils/adt/jsonfuncs.c | 26 ++++++++++++++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_operator.dat | 3 +++
src/include/catalog/pg_proc.dat | 4 +++
src/test/regress/expected/jsonb_jsonpath.out | 20 +++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 4 +++
7 files changed, 72 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c9141..7f4b8970475 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15699,6 +15699,20 @@ table2-mapping
<returnvalue>t</returnvalue>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>jsonb</type> <literal>@-></literal> <type>jsonpath</type>
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Returns the result of a JSON value at the specified path as numeric.
+ Raise error if the JSON value is not a numeric.
+ </para>
+ <para>
+ <literal>'{"a":1}'::jsonb @-> 'a' </literal>
+ <returnvalue>1</returnvalue>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..5393df0ed7f 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -886,6 +886,32 @@ json_object_field_text(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+Datum
+jsonb_object_field_numeric(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ JsonbValue *v;
+ JsonbValue vbuf;
+
+ if (!JB_ROOT_IS_OBJECT(jb))
+ PG_RETURN_NULL();
+
+ v = getKeyJsonValueFromContainer(&jb->root,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+
+ if (v == NULL || v->type == jbvNull)
+ PG_RETURN_NULL();
+
+ if (v->type != jbvNumeric)
+ elog(ERROR, "field '%s' has non-numeric value.", text_to_cstring(key));
+
+ return PointerGetDatum(v->val.numeric);
+};
+
+
Datum
jsonb_object_field_text(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb28..5a534771edb 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202307261
+#define CATALOG_VERSION_NO 202308011
#endif
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index b2cdea66c4b..d63b9f5188d 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3178,6 +3178,9 @@
{ oid => '3477', descr => 'get jsonb object field as text',
oprname => '->>', oprleft => 'jsonb', oprright => 'text', oprresult => 'text',
oprcode => 'jsonb_object_field_text' },
+{ oid => '3814', descr => 'get jsonb object field as numeric',
+ oprname => '@->', oprleft => 'jsonb', oprright => 'text', oprresult => 'numeric',
+ oprcode => 'jsonb_object_field_numeric' },
{ oid => '3212', descr => 'get jsonb array element',
oprname => '->', oprleft => 'jsonb', oprright => 'int4', oprresult => 'jsonb',
oprcode => 'jsonb_array_element' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..e93303f3be7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9928,6 +9928,10 @@
proname => 'jsonb_object_field_text', prorettype => 'text',
proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
prosrc => 'jsonb_object_field_text' },
+{ oid => '3813',
+ proname => 'jsonb_object_field_numeric', prorettype => 'numeric',
+ proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
+ prosrc => 'jsonb_object_field_numeric' },
{ oid => '3215',
proname => 'jsonb_array_element', prorettype => 'jsonb',
proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 6659bc9091a..bf2351cf9cf 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -34,6 +34,26 @@ select jsonb '{"a": 12}' @? '$.b + 2';
(1 row)
+select jsonb '{"a": 12}' @-> 'a';
+ ?column?
+----------
+ 12
+(1 row)
+
+select pg_typeof(jsonb '{"a": 12}' @-> 'a');
+ pg_typeof
+-----------
+ numeric
+(1 row)
+
+select jsonb '{"a": 12}' @-> 'b';
+ ?column?
+----------
+
+(1 row)
+
+select jsonb '{"a": "12a"}' @-> 'a';
+ERROR: field 'a' has non-numeric value.
select jsonb '{"a": {"a": 12}}' @? '$.a.a';
?column?
----------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index e0ce509264a..32576566f11 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -4,6 +4,10 @@ select jsonb '{"a": 12}' @? '$.a.b';
select jsonb '{"a": 12}' @? '$.b';
select jsonb '{"a": 12}' @? '$.a + 2';
select jsonb '{"a": 12}' @? '$.b + 2';
+select jsonb '{"a": 12}' @-> 'a';
+select pg_typeof(jsonb '{"a": 12}' @-> 'a');
+select jsonb '{"a": 12}' @-> 'b';
+select jsonb '{"a": "12a"}' @-> 'a';
select jsonb '{"a": {"a": 12}}' @? '$.a.a';
select jsonb '{"a": {"a": 12}}' @? '$.*.a';
select jsonb '{"b": {"a": 12}}' @? '$.*.a';
--
2.21.0
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]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@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