Re: pgsql: JSON_TABLE

On Tue, Apr 5, 2022 at 1:31 AM Andrew Dunstan <andrew(at)dunslane(dot)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