pgsql: JSON_TABLE
JSON_TABLE
This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.
Nikita Glukhov
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu (whose
name I previously misspelled), Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby.
Discussion: /messages/by-id/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/4e34747c88a03ede6e9d731727815e37273d4bc9
Modified Files
--------------
src/backend/commands/explain.c | 8 +-
src/backend/executor/execExpr.c | 1 +
src/backend/executor/execExprInterp.c | 18 +-
src/backend/executor/nodeTableFuncscan.c | 23 +-
src/backend/nodes/copyfuncs.c | 85 +++++
src/backend/nodes/equalfuncs.c | 65 ++++
src/backend/nodes/nodeFuncs.c | 27 ++
src/backend/nodes/outfuncs.c | 29 ++
src/backend/nodes/readfuncs.c | 31 ++
src/backend/parser/Makefile | 1 +
src/backend/parser/gram.y | 199 +++++++++-
src/backend/parser/parse_clause.c | 12 +-
src/backend/parser/parse_expr.c | 32 +-
src/backend/parser/parse_jsontable.c | 466 +++++++++++++++++++++++
src/backend/parser/parse_relation.c | 3 +-
src/backend/parser/parse_target.c | 3 +
src/backend/utils/adt/jsonpath_exec.c | 436 +++++++++++++++++++++
src/backend/utils/adt/ruleutils.c | 228 ++++++++++-
src/backend/utils/misc/queryjumble.c | 2 +
src/include/executor/execExpr.h | 4 +
src/include/nodes/nodes.h | 4 +
src/include/nodes/parsenodes.h | 48 +++
src/include/nodes/primnodes.h | 39 +-
src/include/parser/kwlist.h | 3 +
src/include/parser/parse_clause.h | 3 +
src/include/utils/jsonpath.h | 4 +
src/test/regress/expected/json_sqljson.out | 6 +
src/test/regress/expected/jsonb_sqljson.out | 562 ++++++++++++++++++++++++++++
src/test/regress/sql/json_sqljson.sql | 4 +
src/test/regress/sql/jsonb_sqljson.sql | 284 ++++++++++++++
src/tools/pgindent/typedefs.list | 9 +
31 files changed, 2605 insertions(+), 34 deletions(-)
Op 04-04-2022 om 22:23 schreef Andrew Dunstan:
JSON_TABLE
Great that this is now committed!
I notice one changed item: the NESTED-PATH-phrase does not accept an
alias anymore. The JSON_PATH v59 patches still had:
| NESTED PATH json_path_specification [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
My complaint is only half-hearted because I don't really understand what
the use of such nested-path aliases are. But it's a change from the
earlier patch, and the nested-path aliases are used too in the
2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have
to a SQL Standard description.
FWIW, I attach example sql+data from that .pdf from ISO (which is not
online anymore).
Thanks,
Erik Rijkers
Attachments:
On 4/4/22 18:16, Erik Rijkers wrote:
Op 04-04-2022 om 22:23 schreef Andrew Dunstan:
JSON_TABLE
Great that this is now committed!
I notice one changed item: the NESTED-PATH-phrase does not accept an
alias anymore. The JSON_PATH v59 patches still had:| NESTED PATH json_path_specification [ AS path_name ]
COLUMNS ( json_table_column [, ...] )My complaint is only half-hearted because I don't really understand
what the use of such nested-path aliases are. But it's a change from
the earlier patch, and the nested-path aliases are used too in the
2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have
to a SQL Standard description.FWIW, I attach example sql+data from that .pdf from ISO (which is not
online anymore).
These commits are being staggered. The last code patches will be
committed tomorrow.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On Tue, Apr 5, 2022 at 1:17 AM Erik Rijkers <er@xs4all.nl> wrote:
Op 04-04-2022 om 22:23 schreef Andrew Dunstan:
JSON_TABLE
Great that this is now committed!
I notice one changed item: the NESTED-PATH-phrase does not accept an
alias anymore. The JSON_PATH v59 patches still had:| NESTED PATH json_path_specification [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
This is true.
My complaint is only half-hearted because I don't really understand what
the use of such nested-path aliases are. But it's a change from the
https://github.com/obartunov/sqljsondoc/blob/master/README.jsonpath.md
"Every path may be followed by a path name using an AS clause. Path
names are identifiers and must be unique and don't coincide with the
column names."
SELECT
jt.*
FROM
house,
JSON_TABLE(js, '$.floor[*]' AS lvl COLUMNS (
level int,
NESTED PATH '$.apt[*] ? (@.area > 1000)' AS big COLUMNS (
no int
)
) PLAN (lvl OUTER big) ) jt;
earlier patch, and the nested-path aliases are used too in the
2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have
to a SQL Standard description.FWIW, I attach example sql+data from that .pdf from ISO (which is not
online anymore).Thanks,
Erik Rijkers
--
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On Tue, Apr 5, 2022 at 1:31 AM Andrew Dunstan <andrew@dunslane.net> wrote:
On 4/4/22 18:16, Erik Rijkers wrote:
Op 04-04-2022 om 22:23 schreef Andrew Dunstan:
JSON_TABLE
Great that this is now committed!
I notice one changed item: the NESTED-PATH-phrase does not accept an
alias anymore. The JSON_PATH v59 patches still had:| NESTED PATH json_path_specification [ AS path_name ]
COLUMNS ( json_table_column [, ...] )My complaint is only half-hearted because I don't really understand
what the use of such nested-path aliases are. But it's a change from
the earlier patch, and the nested-path aliases are used too in the
2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have
to a SQL Standard description.FWIW, I attach example sql+data from that .pdf from ISO (which is not
online anymore).These commits are being staggered. The last code patches will be
committed tomorrow.
as for PostgreSQL 15devel-master/fadb48b00e aliases AS works
SELECT
jt.*
FROM
house,
JSON_TABLE(js, '$.floor[*]' AS lvl COLUMNS (
level int,
NESTED PATH '$.apt[*] ? (@.area > 1000)' AS big COLUMNS (
no int
)
) PLAN (lvl OUTER big) ) jt;
level | no
-------+--------
1 | (null)
2 | (null)
(2 rows)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
--
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On 4/5/22 15:05, Oleg Bartunov wrote:
On Tue, Apr 5, 2022 at 1:31 AM Andrew Dunstan <andrew@dunslane.net> wrote:
On 4/4/22 18:16, Erik Rijkers wrote:
Op 04-04-2022 om 22:23 schreef Andrew Dunstan:
JSON_TABLE
Great that this is now committed!
I notice one changed item: the NESTED-PATH-phrase does not accept an
alias anymore. The JSON_PATH v59 patches still had:| NESTED PATH json_path_specification [ AS path_name ]
COLUMNS ( json_table_column [, ...] )My complaint is only half-hearted because I don't really understand
what the use of such nested-path aliases are. But it's a change from
the earlier patch, and the nested-path aliases are used too in the
2017-03 'Technical report ISO/IEC TR 19075-6', which is as near I have
to a SQL Standard description.FWIW, I attach example sql+data from that .pdf from ISO (which is not
online anymore).These commits are being staggered. The last code patches will be
committed tomorrow.as for PostgreSQL 15devel-master/fadb48b00e aliases AS works
SELECT
jt.*
FROM
house,
JSON_TABLE(js, '$.floor[*]' AS lvl COLUMNS (
level int,
NESTED PATH '$.apt[*] ? (@.area > 1000)' AS big COLUMNS (
no int
)
) PLAN (lvl OUTER big) ) jt;
level | no
-------+--------
1 | (null)
2 | (null)
(2 rows)
Yeah, and I think that's the answer to Erik's question about why we need
it, it's so you have a name you can refer to in the PLAN clause, as in
you example.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com