JSON_TABLE: Add support for NESTED paths and columns
authorAmit Langote <amitlan@postgresql.org>
Mon, 8 Apr 2024 06:58:58 +0000 (15:58 +0900)
committerAmit Langote <amitlan@postgresql.org>
Mon, 8 Apr 2024 07:14:13 +0000 (16:14 +0900)
commitbb766cde63b4f624d029b34c9cdd3d0a94fd5b46
tree7637d09824843064c6c1c0cb0bc8504d7e887718
parentf6a2529920cff76cb6e37ea840122574404dde8b
JSON_TABLE: Add support for NESTED paths and columns

A NESTED path allows to extract data from nested levels of JSON
objects given by the parent path expression, which are projected as
columns specified using a nested COLUMNS clause, just like the parent
COLUMNS clause.  Rows comprised from a NESTED columns are "joined"
to the row comprised from the parent columns.  If a particular NESTED
path evaluates to 0 rows, then the nested COLUMNS will emit NULLs,
making it an OUTER join.

NESTED columns themselves may include NESTED paths to allow
extracting data from arbitrary nesting levels, which are likewise
joined against the rows at the parent level.

Multiple NESTED paths at a given level are called "sibling" paths
and their rows are combined by UNIONing them, that is, after being
joined against the parent row as described above.

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: 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

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+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
17 files changed:
doc/src/sgml/func.sgml
src/backend/catalog/sql_features.txt
src/backend/nodes/nodeFuncs.c
src/backend/parser/gram.y
src/backend/parser/parse_jsontable.c
src/backend/utils/adt/jsonpath_exec.c
src/backend/utils/adt/ruleutils.c
src/include/nodes/parsenodes.h
src/include/nodes/primnodes.h
src/include/parser/kwlist.h
src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
src/test/regress/expected/sqljson_jsontable.out
src/test/regress/sql/sqljson_jsontable.sql
src/tools/pgindent/typedefs.list