Op 04-05-2022 om 13:55 schreef Andrew Dunstan:
>
> 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
After some further experimentation, I now think you must be right, David.
Also, looking at the DB2 docs:
https://www.ibm.com/docs/en/i/7.2?topic=data-using-json-table
(see especially under 'Handling nested information')
There, I gathered some example data + statements where one is the case
at hand. I also made them runnable under postgres (attached).
I thought that was an instructive example, with those 'outer_ordinality'
and 'inner_ordinality' columns.
Erik
>> 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
>