MIME-Version: 1.0
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Tue, 1 Aug 2023 12:38:57 +0800
Message-ID: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
Subject: Extract numeric filed in JSONB more effectively
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000ac059f0601d52012"
Archived-At: <https://www.postgresql.org/message-id/CAKU4AWoqAVya6PBhn%2BBCbFaBMt3z-2%3Di5fKO3bW%3D6HPhbid2Dw%40mail.gmail.com>
Precedence: bulk

--000000000000ac059f0601d52012
Content-Type: multipart/alternative; boundary="000000000000ac059b0601d52010"

--000000000000ac059b0601d52010
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ac059b0601d52010
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi:</div><div><br></div>Currently if we want to extra=
ct a numeric field in jsonb, we need to use<br>the following expression: =
=C2=A0cast (a-&gt;&gt;&#39;a&#39; as numeric). It will turn a numeric<div>t=
o text first and then turn the text to numeric again. See=C2=A0</div><div>j=
sonb_object_field_text and=C2=A0JsonbValueAsText.=C2=A0 However the binary =
format</div><div>of numeric in JSONB is compatible with the numeric in SQL,=
 so I think we</div><div>can have an operator to extract the numeric direct=
ly. If the value of a given</div><div>field is not a numeric data type, an =
error will be raised, this can be=C2=A0</div><div>documented.<div><br>In th=
is patch, I added a new operator for this purpose, here is the<br>performan=
ce gain because of this.<br><br>create table tb (a jsonb);<br>insert into t=
b select &#39;{&quot;a&quot;: 1}&#39;::jsonb from generate_series(1, 100000=
)i;<br><br>current method:<br>select count(*) from tb where cast (a-&gt;&gt=
;&#39;a&#39; as numeric) =3D 2;<br>167ms.<br><br>new method:<br>select coun=
t(*) from tb where a@-&gt;&#39;a&#39; =3D 2;<br>65ms.<br><br>Is this the ri=
ght way to go? Testcase, document and catalog version are<br>updated.<div><=
br clear=3D"all"><div><br></div><span class=3D"gmail_signature_prefix">-- <=
/span><br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmai=
l_signature"><div dir=3D"ltr">Best Regards<div>Andy Fan</div></div></div></=
div></div></div></div>

--000000000000ac059b0601d52010--
--000000000000ac059f0601d52012
Content-Type: application/octet-stream; 
	name="v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch"
Content-Disposition: attachment; 
	filename="v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lkrt16b00>
X-Attachment-Id: f_lkrt16b00

RnJvbSAxNDE1ZTA5ZDZjYTg2MGQ0NjY5MzkyMjllNmQ2YTkwMTJiZjJiYmNmIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbmR5IEZhbiA8emhpaHVpLmZhbjEyMTNAZ21haWwuY29tPgpE
YXRlOiBUdWUsIDEgQXVnIDIwMjMgMTA6Mzg6MjkgKzA4MDAKU3ViamVjdDogW1BBVENIIHYxXSBB
ZGQganNvbmIgb3BlcmF0b3IgdG8gcmV0dXJuIGEgbnVtZXJpYyBkaXJlY3RseS4KClRoZSBiaW5h
cnkgZm9ybWF0IG9mIG51bWVyaWMgaW4gSk9TTkIgaXMgY29tcGF0aWJsZSB3aXRoIHRoZSBudW1l
cmljCmluIFNRTCwgc28gd2UgY2FuIGdldCB0aGUgbnVtZXJpYyBtb3JlIGVmZmVjdGl2ZWx5Lgot
LS0KIGRvYy9zcmMvc2dtbC9mdW5jLnNnbWwgICAgICAgICAgICAgICAgICAgICAgIHwgMTQgKysr
KysrKysrKysKIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uZnVuY3MuYyAgICAgICAgICAgIHwg
MjYgKysrKysrKysrKysrKysrKysrKysKIHNyYy9pbmNsdWRlL2NhdGFsb2cvY2F0dmVyc2lvbi5o
ICAgICAgICAgICAgIHwgIDIgKy0KIHNyYy9pbmNsdWRlL2NhdGFsb2cvcGdfb3BlcmF0b3IuZGF0
ICAgICAgICAgIHwgIDMgKysrCiBzcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0ICAgICAg
ICAgICAgICB8ICA0ICsrKwogc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qc29uYl9qc29ucGF0
aC5vdXQgfCAyMCArKysrKysrKysrKysrKysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pz
b25wYXRoLnNxbCAgICAgIHwgIDQgKysrCiA3IGZpbGVzIGNoYW5nZWQsIDcyIGluc2VydGlvbnMo
KyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sIGIv
ZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbAppbmRleCBiZTJmNTRjOTE0MS4uN2Y0Yjg5NzA0NzUgMTAw
NjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2Z1bmMu
c2dtbApAQCAtMTU2OTksNiArMTU2OTksMjAgQEAgdGFibGUyLW1hcHBpbmcKICAgICAgICAgPHJl
dHVybnZhbHVlPnQ8L3JldHVybnZhbHVlPgogICAgICAgIDwvcGFyYT48L2VudHJ5PgogICAgICAg
PC9yb3c+CisgICAgICA8cm93PgorICAgICAgIDxlbnRyeSByb2xlPSJmdW5jX3RhYmxlX2VudHJ5
Ij48cGFyYSByb2xlPSJmdW5jX3NpZ25hdHVyZSI+CisgICAgICAgIDx0eXBlPmpzb25iPC90eXBl
PiA8bGl0ZXJhbD5ALT48L2xpdGVyYWw+IDx0eXBlPmpzb25wYXRoPC90eXBlPgorICAgICAgICA8
cmV0dXJudmFsdWU+bnVtZXJpYzwvcmV0dXJudmFsdWU+CisgICAgICAgPC9wYXJhPgorICAgICAg
IDxwYXJhPgorICAgICAgICBSZXR1cm5zIHRoZSByZXN1bHQgb2YgYSBKU09OIHZhbHVlIGF0IHRo
ZSBzcGVjaWZpZWQgcGF0aCBhcyBudW1lcmljLgorICAgICAgICBSYWlzZSBlcnJvciBpZiB0aGUg
SlNPTiB2YWx1ZSBpcyBub3QgYSBudW1lcmljLgorICAgICAgIDwvcGFyYT4KKyAgICAgICA8cGFy
YT4KKyAgICAgICAgPGxpdGVyYWw+J3siYSI6MX0nOjpqc29uYiBALT4gJ2EnIDwvbGl0ZXJhbD4K
KyAgICAgICAgPHJldHVybnZhbHVlPjE8L3JldHVybnZhbHVlPgorICAgICAgIDwvcGFyYT48L2Vu
dHJ5PgorICAgICAgPC9yb3c+CiAgICAgIDwvdGJvZHk+CiAgICAgPC90Z3JvdXA+CiAgICA8L3Rh
YmxlPgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jIGIvc3Jj
L2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jCmluZGV4IGE0YmZhNWU0MDQwLi41MzkzZGYw
ZWQ3ZiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jCisrKyBi
L3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uZnVuY3MuYwpAQCAtODg2LDYgKzg4NiwzMiBAQCBq
c29uX29iamVjdF9maWVsZF90ZXh0KFBHX0ZVTkNUSU9OX0FSR1MpCiAJCVBHX1JFVFVSTl9OVUxM
KCk7CiB9CiAKK0RhdHVtCitqc29uYl9vYmplY3RfZmllbGRfbnVtZXJpYyhQR19GVU5DVElPTl9B
UkdTKQoreworCUpzb25iCSAgICpqYiA9IFBHX0dFVEFSR19KU09OQl9QKDApOworCXRleHQJICAg
KmtleSA9IFBHX0dFVEFSR19URVhUX1BQKDEpOworCUpzb25iVmFsdWUgKnY7CisJSnNvbmJWYWx1
ZQl2YnVmOworCisJaWYgKCFKQl9ST09UX0lTX09CSkVDVChqYikpCisJCVBHX1JFVFVSTl9OVUxM
KCk7CisKKwl2ID0gZ2V0S2V5SnNvblZhbHVlRnJvbUNvbnRhaW5lcigmamItPnJvb3QsCisJCQkJ
CQkJCQkgVkFSREFUQV9BTlkoa2V5KSwKKwkJCQkJCQkJCSBWQVJTSVpFX0FOWV9FWEhEUihrZXkp
LAorCQkJCQkJCQkJICZ2YnVmKTsKKworCWlmICh2ID09IE5VTEwgfHwgdi0+dHlwZSA9PSBqYnZO
dWxsKQorCQlQR19SRVRVUk5fTlVMTCgpOworCisJaWYgKHYtPnR5cGUgIT0gamJ2TnVtZXJpYykK
KwkJZWxvZyhFUlJPUiwgImZpZWxkICclcycgaGFzIG5vbi1udW1lcmljIHZhbHVlLiIsIHRleHRf
dG9fY3N0cmluZyhrZXkpKTsKKworCXJldHVybiBQb2ludGVyR2V0RGF0dW0odi0+dmFsLm51bWVy
aWMpOworfTsKKworCiBEYXR1bQoganNvbmJfb2JqZWN0X2ZpZWxkX3RleHQoUEdfRlVOQ1RJT05f
QVJHUykKIHsKZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL2NhdGFsb2cvY2F0dmVyc2lvbi5oIGIv
c3JjL2luY2x1ZGUvY2F0YWxvZy9jYXR2ZXJzaW9uLmgKaW5kZXggZjUwN2I0OWJiMjguLjVhNTM0
NzcxZWRiIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaAorKysg
Yi9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaApAQCAtNTcsNiArNTcsNiBAQAogICov
CiAKIC8qCQkJCQkJCXl5eXltbWRkTiAqLwotI2RlZmluZSBDQVRBTE9HX1ZFUlNJT05fTk8JMjAy
MzA3MjYxCisjZGVmaW5lIENBVEFMT0dfVkVSU0lPTl9OTwkyMDIzMDgwMTEKIAogI2VuZGlmCmRp
ZmYgLS1naXQgYS9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX29wZXJhdG9yLmRhdCBiL3NyYy9pbmNs
dWRlL2NhdGFsb2cvcGdfb3BlcmF0b3IuZGF0CmluZGV4IGIyY2RlYTY2YzRiLi5kNjNiOWY1MTg4
ZCAxMDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19vcGVyYXRvci5kYXQKKysrIGIv
c3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19vcGVyYXRvci5kYXQKQEAgLTMxNzgsNiArMzE3OCw5IEBA
CiB7IG9pZCA9PiAnMzQ3NycsIGRlc2NyID0+ICdnZXQganNvbmIgb2JqZWN0IGZpZWxkIGFzIHRl
eHQnLAogICBvcHJuYW1lID0+ICctPj4nLCBvcHJsZWZ0ID0+ICdqc29uYicsIG9wcnJpZ2h0ID0+
ICd0ZXh0Jywgb3BycmVzdWx0ID0+ICd0ZXh0JywKICAgb3ByY29kZSA9PiAnanNvbmJfb2JqZWN0
X2ZpZWxkX3RleHQnIH0sCit7IG9pZCA9PiAnMzgxNCcsIGRlc2NyID0+ICdnZXQganNvbmIgb2Jq
ZWN0IGZpZWxkIGFzIG51bWVyaWMnLAorICBvcHJuYW1lID0+ICdALT4nLCBvcHJsZWZ0ID0+ICdq
c29uYicsIG9wcnJpZ2h0ID0+ICd0ZXh0Jywgb3BycmVzdWx0ID0+ICdudW1lcmljJywKKyAgb3By
Y29kZSA9PiAnanNvbmJfb2JqZWN0X2ZpZWxkX251bWVyaWMnIH0sCiB7IG9pZCA9PiAnMzIxMics
IGRlc2NyID0+ICdnZXQganNvbmIgYXJyYXkgZWxlbWVudCcsCiAgIG9wcm5hbWUgPT4gJy0+Jywg
b3BybGVmdCA9PiAnanNvbmInLCBvcHJyaWdodCA9PiAnaW50NCcsIG9wcnJlc3VsdCA9PiAnanNv
bmInLAogICBvcHJjb2RlID0+ICdqc29uYl9hcnJheV9lbGVtZW50JyB9LApkaWZmIC0tZ2l0IGEv
c3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19wcm9jLmRhdCBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvcGdf
cHJvYy5kYXQKaW5kZXggNjk5NjA3Mzk4OWEuLmU5MzMwM2YzYmU3IDEwMDY0NAotLS0gYS9zcmMv
aW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0CisrKyBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvcGdf
cHJvYy5kYXQKQEAgLTk5MjgsNiArOTkyOCwxMCBAQAogICBwcm9uYW1lID0+ICdqc29uYl9vYmpl
Y3RfZmllbGRfdGV4dCcsIHByb3JldHR5cGUgPT4gJ3RleHQnLAogICBwcm9hcmd0eXBlcyA9PiAn
anNvbmIgdGV4dCcsIHByb2FyZ25hbWVzID0+ICd7ZnJvbV9qc29uLCBmaWVsZF9uYW1lfScsCiAg
IHByb3NyYyA9PiAnanNvbmJfb2JqZWN0X2ZpZWxkX3RleHQnIH0sCit7IG9pZCA9PiAnMzgxMycs
CisgIHByb25hbWUgPT4gJ2pzb25iX29iamVjdF9maWVsZF9udW1lcmljJywgcHJvcmV0dHlwZSA9
PiAnbnVtZXJpYycsCisgIHByb2FyZ3R5cGVzID0+ICdqc29uYiB0ZXh0JywgcHJvYXJnbmFtZXMg
PT4gJ3tmcm9tX2pzb24sIGZpZWxkX25hbWV9JywKKyAgcHJvc3JjID0+ICdqc29uYl9vYmplY3Rf
ZmllbGRfbnVtZXJpYycgfSwKIHsgb2lkID0+ICczMjE1JywKICAgcHJvbmFtZSA9PiAnanNvbmJf
YXJyYXlfZWxlbWVudCcsIHByb3JldHR5cGUgPT4gJ2pzb25iJywKICAgcHJvYXJndHlwZXMgPT4g
J2pzb25iIGludDQnLCBwcm9hcmduYW1lcyA9PiAne2Zyb21fanNvbiwgZWxlbWVudF9pbmRleH0n
LApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qc29uYl9qc29ucGF0aC5v
dXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pzb25iX2pzb25wYXRoLm91dAppbmRleCA2
NjU5YmM5MDkxYS4uYmYyMzUxY2Y5Y2YgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvanNvbmJfanNvbnBhdGgub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQv
anNvbmJfanNvbnBhdGgub3V0CkBAIC0zNCw2ICszNCwyNiBAQCBzZWxlY3QganNvbmIgJ3siYSI6
IDEyfScgQD8gJyQuYiArIDInOwogIAogKDEgcm93KQogCitzZWxlY3QganNvbmIgJ3siYSI6IDEy
fScgQC0+ICdhJzsKKyA/Y29sdW1uPyAKKy0tLS0tLS0tLS0KKyAgICAgICAxMgorKDEgcm93KQor
CitzZWxlY3QgcGdfdHlwZW9mKGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYScpOworIHBnX3R5cGVv
ZiAKKy0tLS0tLS0tLS0tCisgbnVtZXJpYworKDEgcm93KQorCitzZWxlY3QganNvbmIgJ3siYSI6
IDEyfScgQC0+ICdiJzsKKyA/Y29sdW1uPyAKKy0tLS0tLS0tLS0KKyAgICAgICAgIAorKDEgcm93
KQorCitzZWxlY3QganNvbmIgJ3siYSI6ICIxMmEifScgQC0+ICdhJzsKK0VSUk9SOiAgZmllbGQg
J2EnIGhhcyBub24tbnVtZXJpYyB2YWx1ZS4KIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9
fScgQD8gJyQuYS5hJzsKICA/Y29sdW1uPyAKIC0tLS0tLS0tLS0KZGlmZiAtLWdpdCBhL3NyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pzb25wYXRoLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2pzb25iX2pzb25wYXRoLnNxbAppbmRleCBlMGNlNTA5MjY0YS4uMzI1NzY1NjZmMTEgMTAwNjQ0
Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pzb25wYXRoLnNxbAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL3NxbC9qc29uYl9qc29ucGF0aC5zcWwKQEAgLTQsNiArNCwxMCBAQCBzZWxl
Y3QganNvbmIgJ3siYSI6IDEyfScgQD8gJyQuYS5iJzsKIHNlbGVjdCBqc29uYiAneyJhIjogMTJ9
JyBAPyAnJC5iJzsKIHNlbGVjdCBqc29uYiAneyJhIjogMTJ9JyBAPyAnJC5hICsgMic7CiBzZWxl
Y3QganNvbmIgJ3siYSI6IDEyfScgQD8gJyQuYiArIDInOworc2VsZWN0IGpzb25iICd7ImEiOiAx
Mn0nIEAtPiAnYSc7CitzZWxlY3QgcGdfdHlwZW9mKGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYScp
Oworc2VsZWN0IGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYic7CitzZWxlY3QganNvbmIgJ3siYSI6
ICIxMmEifScgQC0+ICdhJzsKIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9fScgQD8gJyQu
YS5hJzsKIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9fScgQD8gJyQuKi5hJzsKIHNlbGVj
dCBqc29uYiAneyJiIjogeyJhIjogMTJ9fScgQD8gJyQuKi5hJzsKLS0gCjIuMjEuMAoK
--000000000000ac059f0601d52012--