Re: pgsql: JSON_TABLE

On Tue, Apr 5, 2022 at 1:17 AM Erik Rijkers <er(at)xs4all(dot)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