create or replace function random_text() returns text 
as $$select string_agg(md5(random()::text),'') from generate_Series(1,8) s $$ LANGUAGE SQL;
create table s1(a jsonb);
INSERT INTO s1 SELECT (
	'{"id": "' || random_text() || '",'
	'"type": "' || random_text() || '",'
	'"title": "' || random_text() || '",'
	'"original_title": "' || random_text() || '",'
	'"is_adult": "' || random_text() || '",'
	'"start_year": "' || random_text() || '",'
	'"end_year": "' || random_text() || '",'
	'"minutes": "' || random_text() || '",'
	'"genres": "' || random_text() || '",'
	'"aliases": "' || random_text() || '",'
	'"genres": "' || random_text() || '",'
	'"directors": "' || random_text() || '",'
	'"writers": "' || random_text() || '",'
	'"ratings": "' || random_text() || '",'
	'"director_name": "' || random_text() || '",'
	'"alias_title": "' || random_text() || '",'
	'"alias_region": "' || random_text() || '",'
	'"director_birth_year": "' || random_text() || '",'
	'"director_death_year": "' || random_text() || '",'
	'"rating_average": "' || random_text() || '",'
	'"rating_votes": "' || random_text() || '"'
	||'}' )::jsonb
FROM generate_series(1, 1e5);

SELECT pg_size_pretty(pg_table_size('s1')); --598 MB
select count(*) from s1;

/*
another session call it repeatedly:
    select pg_log_backend_memory_contexts(43643) \watch i=4 c=5
*/
SELECT
	json_query(a, '$."id"' returning text),
	json_query(a, '$."type"'  returning text),
	json_query(a, '$."title"' returning text),
	json_query(a, '$."original_title"' returning text),
	json_query(a, '$."is_adult"' returning text),
	json_query(a, '$."start_year"' returning text),
	json_query(a, '$."end_year"' returning text),
	json_query(a, '$."minutes"' returning text),
	json_query(a, '$."genres"' returning text),
	json_query(a, '$."aliases"' returning text),
	json_query(a, '$."directors"' returning text),
	json_query(a, '$."writers"' returning text),
	json_query(a, '$."ratings"' returning text),
	json_query(a, '$."director_name"' returning text),
	json_query(a, '$."alias_title"' returning text),
	json_query(a, '$."alias_region"' returning text),
	json_query(a, '$."director_birth_year"' returning text),
	json_query(a, '$."director_death_year"' returning text),
	json_query(a, '$."rating_average"' returning text),
	json_query(a, '$."rating_votes"' returning text)
from s1;

/*
2024-03-06 19:35:28.879 CST [43643] LOG:  level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 19:35:32.878 CST [43643] LOG:  level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 19:35:36.878 CST [43643] LOG:  level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 19:35:40.878 CST [43643] LOG:  level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
2024-03-06 19:35:44.878 CST [43643] LOG:  level: 3; ExecutorState: 98368 total in 5 blocks; 38128 free (6 chunks); 60240 used
*/
SELECT
	json_value(a, '$."id"' returning text),
	json_value(a, '$."type"'  returning text),
	json_value(a, '$."title"' returning text),
	json_value(a, '$."original_title"' returning text),
	json_value(a, '$."is_adult"' returning text),
	json_value(a, '$."start_year"' returning text),
	json_value(a, '$."end_year"' returning text),
	json_value(a, '$."minutes"' returning text),
	json_value(a, '$."genres"' returning text),
	json_value(a, '$."aliases"' returning text),
	json_value(a, '$."directors"' returning text),
	json_value(a, '$."writers"' returning text),
	json_value(a, '$."ratings"' returning text),
	json_value(a, '$."director_name"' returning text),
	json_value(a, '$."alias_title"' returning text),
	json_value(a, '$."alias_region"' returning text),
	json_value(a, '$."director_birth_year"' returning text),
	json_value(a, '$."director_death_year"' returning text),
	json_value(a, '$."rating_average"' returning text),
	json_value(a, '$."rating_votes"' returning text)
from s1;
/*

2024-03-06 19:38:34.993 CST [43643] LOG:  level: 3; ExecutorState: 196672 total in 6 blocks; 31800 free (4 chunks); 164872 used
2024-03-06 19:38:38.993 CST [43643] LOG:  level: 3; ExecutorState: 196672 total in 6 blocks; 31800 free (4 chunks); 164872 used
2024-03-06 19:38:46.994 CST [43643] LOG:  level: 3; ExecutorState: 196672 total in 6 blocks; 31800 free (4 chunks); 164872 used
2024-03-06 19:38:50.994 CST [43643] LOG:  level: 3; ExecutorState: 196672 total in 6 blocks; 31800 free (4 chunks); 164872 used
2024-03-06 19:38:42.993 CST [43643] LOG:  level: 3; ExecutorState: 196672 total in 6 blocks; 31800 free (4 chunks); 164872 used

*/
SELECT
	jsonb_path_query(a, '$."id"')::TEXT,
	jsonb_path_query(a, '$."type"'  )::text,
	jsonb_path_query(a, '$."title"' )::text,
	jsonb_path_query(a, '$."original_title"' )::text,
	jsonb_path_query(a, '$."is_adult"' )::text,
	jsonb_path_query(a, '$."start_year"' )::text,
	jsonb_path_query(a, '$."end_year"' )::text,
	jsonb_path_query(a, '$."minutes"' )::text,
	jsonb_path_query(a, '$."genres"' )::text,
	jsonb_path_query(a, '$."aliases"' )::text,
	jsonb_path_query(a, '$."directors"' )::text,
	jsonb_path_query(a, '$."writers"' )::text,
	jsonb_path_query(a, '$."ratings"' )::text,
	jsonb_path_query(a, '$."director_name"' )::text,
	jsonb_path_query(a, '$."alias_title"' )::text,
	jsonb_path_query(a, '$."alias_region"' )::text,
	jsonb_path_query(a, '$."director_birth_year"' )::text,
	jsonb_path_query(a, '$."director_death_year"' )::text,
	jsonb_path_query(a, '$."rating_average"' )::text,
	jsonb_path_query(a, '$."rating_votes"' )::text
from s;
/*
2024-03-06 19:40:48.087 CST [43643] LOG:  level: 3; ExecutorState: 336032 total in 8 blocks; 78496 free (5 chunks); 257536 used
2024-03-06 19:40:52.087 CST [43643] LOG:  level: 3; ExecutorState: 336032 total in 8 blocks; 78352 free (2 chunks); 257680 used
2024-03-06 19:40:56.087 CST [43643] LOG:  level: 3; ExecutorState: 336032 total in 8 blocks; 78352 free (2 chunks); 257680 used
2024-03-06 19:41:00.087 CST [43643] LOG:  level: 3; ExecutorState: 336032 total in 8 blocks; 79216 free (20 chunks); 256816 used
2024-03-06 19:41:04.087 CST [43643] LOG:  level: 3; ExecutorState: 336032 total in 8 blocks; 78592 free (7 chunks); 257440 used

*/
SELECT jt.* FROM s t,
	json_table(t.a, '$'
	COLUMNS (
        "id" text path '$."id"',
        "type" text path '$."type"',
        "title" text path '$."title"',
        "original_title" text path '$."original_title"',
        "is_adult" text path '$."is_adult"',
        "start_year" text path '$."start_year"',
        "end_year" text path '$."end_year"',
        "minutes" text path '$."minutes"',
        "genres" text path '$."genres"',
        "aliases" text path '$."aliases"',
        "directors" text path '$."directors"',
        "writers" text path '$."writers"',
        "ratings" text path '$."ratings"',
        "director_name" text path '$."director_name"',
        "alias_title" text path '$."alias_title"',
        "alias_region" text path '$."alias_region"',
        "director_birth_year" text path '$."director_birth_year"',
        "director_death_year" text path '$."director_death_year"',
        "rating_average" text path '$."rating_average"',
        "rating_votes" text path '$."rating_votes"'
	)
) as jt;
/*
2024-03-06 19:42:28.996 CST [43643] LOG:  level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:42:32.996 CST [43643] LOG:  level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:42:36.996 CST [43643] LOG:  level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
2024-03-06 19:42:40.996 CST [43643] LOG:  level: 3; ExecutorState: 278592 total in 7 blocks; 53352 free (6 chunks); 225240 used
2024-03-06 19:42:44.996 CST [43643] LOG:  level: 3; ExecutorState: 278592 total in 7 blocks; 61560 free (7 chunks); 217032 used
*/