drop table if exists jsonb_table_test;
CREATE TEMP TABLE jsonb_table_test (js jsonb);
INSERT INTO jsonb_table_test
VALUES (
	'[
		{"a":  1,  "b": [], "c": []},
		{"a":  2,  "b": [1, 2, 3], "c": [10, null, 20]},
		{"a":  3,  "b": [1, 2], "c": []},
		{"x": "4", "b": [1, 2], "c": 123}
	 ]'
);

/*
select * from outer_union except all select * from inner_union;
select * from outer_union except all select * from outer_cross;
select * from outer_cross except all select * from outer_union;
select * from outer_cross except all select * from inner_union;
select * from outer_union except all select * from inner_union;
------------------
select * from outer_union except all select * from inner_cross;
select * from outer_cross except all select * from inner_cross;
select * from outer_union except all select * from inner_union;
*/

create or replace view outer_union as (
select	jt.*
from
	jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y
		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 '$')
		)
		plan default (outer, union)
	) jt
);

create or replace view outer_cross as (
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 '$' )
		)
		plan default (outer, cross)
	) jt
);

create or replace view inner_union as (
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 '$' )
		)
		plan default (inner, union)
	) jt
);

create or replace view inner_cross as (
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 '$' )
		)
		plan default (inner, cross)
	) jt
);


select * from  inner_cross;
select * from  outer_cross;
select * from  inner_union;
select * from  outer_union;
-----------------------------------------------------
--inner_cross;
select	jt.* from
	jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		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 '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (inner, cross)
	) jt;

--outer_cross;
select	jt.* from
	jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a' default -1 on empty,
			nested path 'strict $.b[*]' as pb columns ( b int path '$?(@ == $"x")' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (outer, cross)
	) jt;

--inner_union;
select	jt.* from
	jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a' default -1 on empty,
			nested path 'strict $.b[*]' as pb columns ( b int path '$?(@ == $"x")' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (inner, union)
	) jt;

--outer_union;
select	jt.* from
	jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a' default -1 on empty,
			nested path 'strict $.b[*]' as pb columns ( b int path '$?(@ == $"x")' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (outer, union)
	) jt;

----------------------------------------
select	jt.* from jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a ' default -1 on empty,
			nested path 'strict $.b[*] ?(@ != 3)' as pb columns ( b int path '$' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (outer, cross)
	) jt;

select jt.* from jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a ' default -1 on empty,
			nested path 'strict $.b[*] ?(@ != 3)' as pb columns ( b int path '$' default 0 on empty),
			-- nested path 'strict $.b[*] ' as pb columns ( b int path '$' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (inner, cross)		
	) jt;

select jt.* from jsonb_table_test jtt,
	json_table (
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a ' default -1 on empty,
			nested path 'strict $.b[*] ?(@ != 3)' as pb columns ( b int path '$' default 0 on empty),
			-- nested path 'strict $.b[*] ' as pb columns ( b int path '$' default 0 on empty),
			nested path 'strict $.c[*]' as pc columns ( c int path '$?(@ == $"x" * 5 || @ == $"y" )' default 0 on empty)
		)
		plan default (inner, union)
	) jt;

SELECT * from JSON_TABLE(jsonb  '[1, 2, 3,null, 3]', '$[*]? (@ == 3)' PASSING 3 as x COLUMNS(xx int path '$'));

select jt.* from jsonb_table_test jtt, json_table(
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a ' default -1 on empty,
			nested path 'strict $.b[*] ?(@ == 3)' as pb columns ( b int path '$'),
			nested path 'strict $.c[*] ? (@ == 20)' as pc columns ( c int path '$')
		)
		plan default (outer, union)
	) jt;

select jt.* from jsonb_table_test jtt, json_table(
		jtt.js,'strict $[*]' as p passing 2 as x, 20 as y, 3 as z
		columns (
			n for ordinality,
			a int path 'lax $.a ' default -1 on empty,
			-- nested path 'strict $.b[*] ?(@ == 3)' as pb columns ( b int path '$'),
			nested path 'strict $.b[*]' as pb columns ( b int path '$'),
			nested path 'strict $.c[*] ? (@ == 20)' as pc columns ( c int path '$')
		)
		plan default (outer, union)
	) jt;