pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: JSON_TABLE
6+ messages / 3 participants
[nested] [flat]
* pgsql: JSON_TABLE
@ 2022-04-04 20:23 Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Re: pgsql: JSON_TABLE Erik Rijkers <er@xs4all.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2022-04-04 20:23 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: JSON_TABLE
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
@ 2022-04-04 22:16 ` Erik Rijkers <er@xs4all.nl>
2022-04-04 22:31 ` Re: pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-05 19:02 ` Re: pgsql: JSON_TABLE Oleg Bartunov <obartunov@postgrespro.ru>
0 siblings, 2 replies; 6+ messages in thread
From: Erik Rijkers @ 2022-04-04 22:16 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; pgsql-committers@lists.postgresql.org
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:
bugmaybe.sh (application/x-shellscript, 4.0K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: JSON_TABLE
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Re: pgsql: JSON_TABLE Erik Rijkers <er@xs4all.nl>
@ 2022-04-04 22:31 ` Andrew Dunstan <andrew@dunslane.net>
2022-04-05 19:05 ` Re: pgsql: JSON_TABLE Oleg Bartunov <obartunov@postgrespro.ru>
1 sibling, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2022-04-04 22:31 UTC (permalink / raw)
To: Erik Rijkers <er@xs4all.nl>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: JSON_TABLE
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Re: pgsql: JSON_TABLE Erik Rijkers <er@xs4all.nl>
2022-04-04 22:31 ` Re: pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
@ 2022-04-05 19:05 ` Oleg Bartunov <obartunov@postgrespro.ru>
2022-04-05 19:35 ` Re: pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 6+ messages in thread
From: Oleg Bartunov @ 2022-04-05 19:05 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Erik Rijkers <er@xs4all.nl>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: JSON_TABLE
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Re: pgsql: JSON_TABLE Erik Rijkers <er@xs4all.nl>
2022-04-04 22:31 ` Re: pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-05 19:05 ` Re: pgsql: JSON_TABLE Oleg Bartunov <obartunov@postgrespro.ru>
@ 2022-04-05 19:35 ` Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Dunstan @ 2022-04-05 19:35 UTC (permalink / raw)
To: Oleg Bartunov <obartunov@postgrespro.ru>; +Cc: Erik Rijkers <er@xs4all.nl>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: JSON_TABLE
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Re: pgsql: JSON_TABLE Erik Rijkers <er@xs4all.nl>
@ 2022-04-05 19:02 ` Oleg Bartunov <obartunov@postgrespro.ru>
1 sibling, 0 replies; 6+ messages in thread
From: Oleg Bartunov @ 2022-04-05 19:02 UTC (permalink / raw)
To: Erik Rijkers <er@xs4all.nl>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-04-05 19:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-04 20:23 pgsql: JSON_TABLE Andrew Dunstan <andrew@dunslane.net>
2022-04-04 22:16 ` Erik Rijkers <er@xs4all.nl>
2022-04-04 22:31 ` Andrew Dunstan <andrew@dunslane.net>
2022-04-05 19:05 ` Oleg Bartunov <obartunov@postgrespro.ru>
2022-04-05 19:35 ` Andrew Dunstan <andrew@dunslane.net>
2022-04-05 19:02 ` Oleg Bartunov <obartunov@postgrespro.ru>
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