From: Amit Langote <amitlan@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add SQL/JSON query functions
Date: Thu, 21 Mar 2024 08:07:37 +0000
Message-ID: <E1rnDSa-004uGu-Kh@gemulon.postgresql.org> (raw)
Add SQL/JSON query functions
This introduces the following SQL/JSON functions for querying JSON
data using jsonpath expressions:
JSON_EXISTS(), which can be used to apply a jsonpath expression to a
JSON value to check if it yields any values.
JSON_QUERY(), which can be used to to apply a jsonpath expression to
a JSON value to get a JSON object, an array, or a string. There are
various options to control whether multi-value result uses array
wrappers and whether the singleton scalar strings are quoted or not.
JSON_VALUE(), which can be used to apply a jsonpath expression to a
JSON value to return a single scalar value, producing an error if it
multiple values are matched.
Both JSON_VALUE() and JSON_QUERY() functions have options for
handling EMPTY and ERROR conditions, which can be used to specify
the behavior when no values are matched and when an error occurs
during jsonpath evaluation, respectively.
Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Peter Eisentraut <peter@eisentraut.org>
Author: Jian He <jian.universality@gmail.com>
Reviewers have included (in no particular order):
Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup,
Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby, Álvaro Herrera, Jian He, Anton A. Melnikov,
Nikita Malakhov, Peter Eisentraut, Tomas Vondra
Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/6185c9737cf48c9540782d88f12bd2912d6ca1cc
Modified Files
--------------
doc/src/sgml/func.sgml | 210 ++++
src/backend/catalog/sql_features.txt | 12 +-
src/backend/executor/execExpr.c | 301 +++++
src/backend/executor/execExprInterp.c | 338 +++++-
src/backend/jit/llvm/llvmjit_expr.c | 108 ++
src/backend/jit/llvm/llvmjit_types.c | 3 +
src/backend/nodes/makefuncs.c | 16 +
src/backend/nodes/nodeFuncs.c | 159 +++
src/backend/optimizer/path/costsize.c | 3 +-
src/backend/optimizer/util/clauses.c | 20 +
src/backend/parser/gram.y | 188 +++-
src/backend/parser/parse_expr.c | 553 +++++++++-
src/backend/parser/parse_target.c | 18 +
src/backend/utils/adt/formatting.c | 44 +
src/backend/utils/adt/jsonb.c | 31 +
src/backend/utils/adt/jsonfuncs.c | 62 +-
src/backend/utils/adt/jsonpath.c | 281 +++++
src/backend/utils/adt/jsonpath_exec.c | 322 ++++++
src/backend/utils/adt/ruleutils.c | 138 +++
src/include/executor/execExpr.h | 22 +
src/include/nodes/execnodes.h | 73 ++
src/include/nodes/makefuncs.h | 2 +
src/include/nodes/parsenodes.h | 42 +
src/include/nodes/primnodes.h | 122 +++
src/include/parser/kwlist.h | 11 +
src/include/utils/formatting.h | 1 +
src/include/utils/jsonb.h | 1 +
src/include/utils/jsonfuncs.h | 7 +
src/include/utils/jsonpath.h | 24 +
src/interfaces/ecpg/preproc/ecpg.trailer | 28 +
src/test/regress/expected/sqljson_queryfuncs.out | 1269 ++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/sqljson_queryfuncs.sql | 428 ++++++++
src/tools/pgindent/typedefs.list | 12 +
34 files changed, 4815 insertions(+), 36 deletions(-)
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: pgsql-committers@postgresql.org
Cc: amitlan@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add SQL/JSON query functions
In-Reply-To: <E1rnDSa-004uGu-Kh@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox