JSONB docs patch
Hello,
I have come across some missing documentation that I think could benefit
the community.
Several functions like `jsonb_exists`, `jsonb_exists_any`,
`jsonb_exists_all` have existed for many PG versions but were not
documented. They are equivalent to `?`, `?|`, and `?&` operators. But some
JDBC drivers have issues with native queries containing these operators
(see
https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb),
so it is useful for users of PG to know the function equivalents of these
operators.
I have attached the patch as an attachment to this email. The documentation
builds correctly without any lint errors after applying the patch locally.
This is my first time contributing, so let me know if there is anything
else I should do (add to commitfest etc).
Cheers!
Mikhail Dobrinin
Attachments:
jsonb-docs-v1.diffapplication/octet-stream; name=jsonb-docs-v1.diffDownload
From 2fbd80dafe72b27fd604952328814dcce297704c Mon Sep 17 00:00:00 2001
From: Mikhail Dobrinin <mvdobrinin@gmail.com>
Date: Tue, 25 Jan 2022 16:10:53 -0600
Subject: [PATCH] Add documentation for several jsonb functions.
Several functions like `jsonb_exists`, `jsonb_exists_any`, `jsonb_exists_all` have existed for many PG versions but were not documented. They are equivalent to `?`, `?|`, and `?&` operators. But some JDBC drivers have issues with native queries containing these operators (see https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb), so it is useful for users of PG to know the function equivalents of these operators.
---
doc/src/sgml/func.sgml | 65 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 65 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0ee6974f1c..fdcb9abb6d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16593,6 +16593,71 @@ table2-mapping
<returnvalue>t</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>jsonb_exists</primary>
+ </indexterm>
+ <function>jsonb_exists</function> ( <type>jsonb</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Checks if the text string exists as a top-level key or array element within the JSON value.
+ (This is functionally equivalent to the jsonb <literal>?</literal>
+ operator, but may be useful to call as a function in some cases, like when working with JDBC protocol.)
+ </para>
+ <para>
+ <literal>jsonb_exists('{"a":1, "b":2}'::jsonb, 'b')</literal>
+ <returnvalue>t</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_exists('["a","b"]'::jsonb, 'b')</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>jsonb_exists_any</primary>
+ </indexterm>
+ <function>jsonb_exists_any</function> ( <type>jsonb</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Checks if any of the strings in the text array exist as top-level keys or array elements within the
+ JSON value. (This is functionally equivalent to the jsonb <literal>?|</literal>
+ operator, but may be useful to call as a function in some cases, like when working with JDBC protocol.)
+ </para>
+ <para>
+ <literal>jsonb_exists_any('{"a":1, "b":2, "c":3}'::jsonb, array['b', 'd'])</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>jsonb_exists_all</primary>
+ </indexterm>
+ <function>jsonb_exists_all</function> ( <type>jsonb</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Checks if all of the strings in the text array exist as top-level keys or array elements within the
+ JSON value. (This is functionally equivalent to the jsonb <literal>?&</literal>
+ operator, but may be useful to call as a function in some cases, like when working with JDBC protocol.)
+ </para>
+ <para>
+ <literal>jsonb_exists_all('{"a":1, "b":2, "c":3}'::jsonb, array['b', 'd'])</literal>
+ <returnvalue>f</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_exists_all('{"a":1, "b":2, "c":3}'::jsonb, array['b', 'c'])</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
--
2.20.1
On Tue, Jan 25, 2022 at 3:38 PM Mikhail Dobrinin <mvdobrinin@gmail.com>
wrote:
Hello,
I have come across some missing documentation that I think could benefit
the community.Several functions like `jsonb_exists`, `jsonb_exists_any`,
`jsonb_exists_all` have existed for many PG versions but were not
documented. They are equivalent to `?`, `?|`, and `?&` operators. But some
JDBC drivers have issues with native queries containing these operators
(see
https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb),
so it is useful for users of PG to know the function equivalents of these
operators.I have attached the patch as an attachment to this email. The
documentation builds correctly without any lint errors after applying
the patch locally. This is my first time contributing, so let me know if
there is anything else I should do (add to commitfest etc).
I'm doubtful that encouraging use of these functions for JDBC-users is
better than them learning to write queries using the proper operator. The
reality is that the usage of indexes depends upon operators being used in
query text, not function names (unless you define a functional index, which
doesn't happen). Your SO post says as much and does mention that ?? is
indeed the coding that is required.
What I think we should do in light of this reality, though, is indeed
prohibit "??" as (or within) an operator in PostgreSQL. Since core is not
presently using that operator its prohibition should be reasonably simple -
though maybe extension authors got too creative?
-1 to this patch on the above grounds. As for the patch itself:
The parentheticals you wrote might be appropriate for a commit message but
do not belong in the documentation. Mentioning JDBC is simply a no-no; and
we don't document "why" we decided to document something. We also don't go
around pointing out what functions and operators perform the same behavior
(mostly because we generally just don't do that, see above).
I didn't actually review the material parts of the table. Nothing seems
obviously incorrect there though.
David J.
On 1/25/22 18:08, David G. Johnston wrote:
On Tue, Jan 25, 2022 at 3:38 PM Mikhail Dobrinin
<mvdobrinin@gmail.com> wrote:Hello,
I have come across some missing documentation that I think could
benefit the community.Several functions like `jsonb_exists`, `jsonb_exists_any`,
`jsonb_exists_all` have existed for many PG versions but were not
documented. They are equivalent to `?`, `?|`, and `?&` operators.
But some JDBC drivers have issues with native queries containing
these operators (see
https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb),
so it is useful for users of PG to know the function equivalents
of these operators.I have attached the patch as an attachment to this email. The
documentation builds correctly without any lint errors after
applying the patch locally. This is my first time contributing, so
let me know if there is anything else I should do (add to
commitfest etc).I'm doubtful that encouraging use of these functions for JDBC-users is
better than them learning to write queries using the proper operator.
The reality is that the usage of indexes depends upon operators being
used in query text, not function names (unless you define a functional
index, which doesn't happen). Your SO post says as much and does
mention that ?? is indeed the coding that is required.What I think we should do in light of this reality, though, is indeed
prohibit "??" as (or within) an operator in PostgreSQL. Since core is
not presently using that operator its prohibition should be reasonably
simple - though maybe extension authors got too creative?-1 to this patch on the above grounds. As for the patch itself:
The parentheticals you wrote might be appropriate for a commit message
but do not belong in the documentation. Mentioning JDBC is simply a
no-no; and we don't document "why" we decided to document something.
We also don't go around pointing out what functions and operators
perform the same behavior (mostly because we generally just don't do
that, see above).I didn't actually review the material parts of the table. Nothing
seems obviously incorrect there though.
Yeah. The omission from the docs is not accidental - we generally don't
document the functions underlying operators.
These jsonb operators are not the only ones with '?' in the name -
there's a whole heap of them for geometric types. Are we supposed to
document all those too?
I feel the pain that JDBC users have here. It's a pity there are no
alternative placeholder mechanisms in JDBC. Perl's DBD::Pg, which also
has '?' placeholders, provides a couple of quite workable ways around
the issue.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com