On 2022-05-03 Tu 20:39, David G. Johnston wrote: > On Tue, May 3, 2022 at 5:27 PM Andrew Dunstan <and...@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
