hi.
`
| NESTED [ PATH ] json_path_specification [ AS json_path_name ]
COLUMNS ( json_table_column [, ...] )
NESTED [ PATH ] json_path_specification [ AS json_path_name ] COLUMNS
( json_table_column [, ...] )
`
"json_path_specification" should be "path_expression"?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
drop table s1;
create or replace function random_text_1000() returns text
as $$select string_agg(md5(random()::text),'') from
generate_Series(1,1000) s $$ LANGUAGE SQL;
create unlogged table s1(a int GENERATED BY DEFAULT AS IDENTITY, js jsonb);
insert into s1(js)
select jsonb ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,' || g
|| ']},{"z22": [32, 204,145]}]},"c": ' || g
|| ',"id": "' || random_text_1000() || '"}')
from generate_series(1_000_000, 1_000_000) g;
insert into s1(js)
select jsonb ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,' || g
|| ']},{"z22": [32, 204,145]}]},"c": ' || g
|| ',"id": "' || random_text_1000() || '"}')
from generate_series(235, 235 + 200000,1) g;
select count(*), pg_size_pretty(pg_total_relation_size('s1')) from s1;
count | pg_size_pretty
--------+----------------
200002 | 6398 MB
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
explain(analyze, costs off,buffers, timing)
SELECT sub.*, s.a as s_a FROM s,
(values(23)) x(x),
generate_series(13, 13) y,
JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
COLUMNS (
xx1 int PATH '$.c',
NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS
(c int PATH '$')),
NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'),
NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x"
-2))' as z1 COLUMNS (a int PATH '$')),
NESTED PATH '$.a.za[1]' COLUMNS
(NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int
PATH '$ ? (@ <= ($"x" + 999976))' default -1000 ON EMPTY))
)) sub;
for one jsonb, it can expand to 7 rows, the above query will return
around 1.4 million rows.
i use the above query, and pg_log_backend_memory_contexts in another
session to check the memory usage.
didn't find memory over consumed issue.
but I am not sure this is the right way to check the memory consumption.
----------------------------------------------------------------------------------------------------------------------
begin;
SELECT sub.*, s.a as s_a FROM s,
(values(23)) x(x),
generate_series(13, 13) y,
JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
COLUMNS (
xx1 int PATH '$.c',
NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS
(c int PATH '$')),
NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'),
NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x"
-2))' as z1 COLUMNS (a int PATH '$')),
NESTED PATH '$.a.za[1]' COLUMNS
(NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int
PATH '$ ? (@ <= ($"x" + 999976))' error ON EMPTY))
)) sub;
rollback;
only the last row will fail, because of "error ON EMPTY", (1_000_000
<= 23 + 999976) is false.
I remember the very previous patch, because of error cleanup, it took
a lot of resources.
does our current implementation, only the very last row fail, will it
be easy to clean up the transaction?
the last query error message is:
`
ERROR: no SQL/JSON item
`
we are in ExecEvalJsonExprPath, can we output it to be:
`
ERROR: after applying json_path "5s", no SQL/JSON item found
`
in a json_table query, we can have multiple path_expressions, like the
above query.
it's not easy to know applying which path_expression failed.
