Re: pgsql: JSON_TABLE

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