On 2022-05-03 Tu 20:39, David G. Johnston wrote:
> On Tue, May 3, 2022 at 5:27 PM Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> On 2022-05-03 Tu 11:19, Erik Rijkers wrote:
> > Hi
> >
> > I've copied some statements from the .pdf called:
> > "TECHNICAL REPORT ISO/IEC TR 19075-6 First edition 2017-03
> > Part SQL Notation support 6: (JSON) for JavaScript Object"
> > (not available anymore although there should be a similar
> replacement
> > file)
> >
> > In that pdf I found the data and statement (called 'table 15' in the
> > .pdf) as in the attached bash file. But the result is different: as
> > implemented by 15devel, the column rowseq is always 1. It seems
> to me
> > that that is wrong; it should count 1, 2, 3 as indeed the
> > example-result column in that pdf shows.
> >
> > What do you think?
> >
> >
>
> Possibly.
>
>
> I don't see how rowseq can be anything but 1. Each invocation of
> json_table is given a single jsonb record via the lateral reference to
> bookclub.jcol. It produces one result, having a rowseq 1. It does
> this for all three outer lateral reference tuples and thus produces
> three output rows each with one match numbered rowseq 1.
>
I imagine we could overcome that by stashing the sequence counter
somewhere it would survive across calls. The question really is what is
the right thing to do? I'm also a bit worried about how correct is
ordinal numbering with nested paths, e.g. (from the regression tests):
select
jt.*
from
jsonb_table_test jtt,
json_table (
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
a int path 'lax $.a' default -1 on empty,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
) jt;
n | a | b | c
---+----+---+----
1 | 1 | |
2 | 2 | 1 |
2 | 2 | 2 |
2 | 2 | 3 |
2 | 2 | | 10
2 | 2 | |
2 | 2 | | 20
3 | 3 | 1 |
3 | 3 | 2 |
4 | -1 | 1 |
4 | -1 | 2 |
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com