pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: JSON_TABLE
6+ messages / 3 participants
[nested] [flat]

* pgsql: JSON_TABLE
@ 2022-04-04 20:23  Andrew Dunstan <andrew@dunslane.net>
  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 22:16  Erik Rijkers <er@xs4all.nl>
  parent: Andrew Dunstan <andrew@dunslane.net>
  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 22:31  Andrew Dunstan <andrew@dunslane.net>
  parent: Erik Rijkers <er@xs4all.nl>
  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-05 19:02  Oleg Bartunov <obartunov@postgrespro.ru>
  parent: Erik Rijkers <er@xs4all.nl>
  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

* Re: pgsql: JSON_TABLE
@ 2022-04-05 19:05  Oleg Bartunov <obartunov@postgrespro.ru>
  parent: 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-05 19:35  Andrew Dunstan <andrew@dunslane.net>
  parent: Oleg Bartunov <obartunov@postgrespro.ru>
  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


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