after_apply_jsonpathvar.sql
application/sql
Filename: after_apply_jsonpathvar.sql
Type: application/sql
Part: 1
Message:
Re: remaining sql/json patches
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;