Re: pgsql: JSON_TABLE

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