Thread: 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: https://postgr.es/m/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
Attachment
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