Re: remaining sql/json patches

On Thu, Apr 4, 2024 at 2:41 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Wed, Apr 3, 2024 at 8:39 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > Attached updated patches. I have addressed your doc comments on 0001,
> > but not 0002 yet.
> >
>
about v49, 0002.

--tests setup.
drop table if exists s cascade;
create table s(js jsonb);
insert into s values
('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32,
204,145]}]},"c": 3}'),
('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}');

after playing around, I found, the non-nested column will be sorted first,
and the nested column will be ordered as is.
the below query, column "xx1" will be the first column, "xx" will be
the second column.

SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y,
JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS(
NESTED PATH '$.a.za[2]' as n3 columns (NESTED PATH '$.z22[*]' as z22
COLUMNS (c int path '$')),
NESTED PATH '$.a.za[1]' as n4 columns (d int[] PATH '$.z21'),
NESTED PATH '$.a.za[0]' as n1 columns (NESTED PATH '$.z1[*]' as z1
COLUMNS (a int path '$')),
xx1 int path '$.c',
NESTED PATH '$.a.za[1]' as n2 columns (NESTED PATH '$.z21[*]' as z21
COLUMNS (b int path '$')),
xx int path '$.c'
))sub;
maybe this behavior is fine. but there is no explanation?
--------------------------------------------------------------------------------
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1327,6 +1327,7 @@ JsonPathMutableContext
JsonPathParseItem
JsonPathParseResult
JsonPathPredicateCallback
+JsonPathSpec
this change is no need.

--------------------------------------------------------------------------------
+ if (scan->child)
+ get_json_table_nested_columns(tf, scan->child, context, showimplicit,
+ scan->colMax >= scan->colMin);
except parse_jsontable.c, we only use colMin, colMax in get_json_table_columns.
aslo in parse_jsontable.c, we do it via:

+ /* Start of column range */
+ colMin = list_length(tf->colvalexprs);
....
+ /* End of column range */
+ colMax = list_length(tf->colvalexprs) - 1;

maybe we can use (bool *) to tag whether this JsonTableColumn is nested or not
in transformJsonTableColumns.

currently colMin, colMax seems to make parsing back json_table (nested
path only) not working.
--------------------------------------------------------------------------------
I also added some slightly complicated tests to prove that the PASSING
clause works
with every level, aslo the multi level nesting clause works as intended.

As mentioned in the previous mail, parsing back nest columns
json_table expression
not working as we expected.

so the last view (jsonb_table_view7) I added, the view definition is WRONG!!
the good news is the output is what we expected, the coverage is pretty high.