public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Thu, 3 Aug 2023 08:50:47 +0800
Message-ID: <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com> (raw)
In-Reply-To: <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
Hi Jian:
> return PointerGetDatum(v->val.numeric);
> should be something like
> PG_RETURN_NUMERIC(v->val.numeric);
> ?
>
Thanks for this reminder, a new patch is attached. and commitfest
entry is added as well[1]. For recording purposes, I compared the
new operator with all the existing operators.
select 1 from tb where (a->'a')::numeric = 2; 30.56ms
select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
select 1 from tb where (a@->'a') = 2; 14.80ms
[1] https://commitfest.postgresql.org/44/4476/
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v2-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch (5.7K, 3-v2-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
view thread (8+ messages) latest in thread
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], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@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