NORM(NO ORM) transform two one-many relationships into a single json.

https://stackoverflow.com/questions/71291056/plpgsql-one-input-get-all-the-related-feilds-columns-to-json-failed

*relationship: one country-> many states. one state-> many cities. *
Basic idea, a function: input one country_id element, transformed all the
relevant country, state, city level information into a single json.
Propagate twice, from country_id to state_id, state_id to city_id. Then the
country_id need be joined twice. when we do array_agg on state level, we
need explicitly *join country_id*, during city level we also need using *join
country_id*.
Reference link: https://github.com/hettie-d/NORM/tree/master/sql
sql file also attached. Column width is quite large, but it's more
readable.

begin;create table public.country(country_id bigint primary key , name
text, leader text);create table public.states(state_id bigint primary
key, name text, population bigint,country_id bigint REFERENCES
public.country (country_id));create table public.cities(city_id
bigint,name text,state_id bigint REFERENCES public.states
(state_id));insert into public.country values ( 1, 'India',
'Narendra Modi');insert into public.country values ( 2 , 'USA', 'Joe
Biden');insert into public.country values ( 3 , 'Australia', 'Scott
Morrison');insert into public.states values( 1 ,'California'
, 39500000 , 2);insert into public.states values( 2 ,
'Washington' , 7610000 ,2 );insert into public.states
values( 4 , 'Karnataka' , 64100000,1);insert into
public.states values( 5 , 'Rajasthan' , 68900000,1
);insert into public.states values( 6 , 'Maharashtra' ,
125700000,1 );insert into public.cities values( 1 , 'Mumbai'
, 6 );insert into public.cities values( 2 , 'Pune'
, 6 );insert into public.cities values( 3 , 'San Francisco'
, 1 );commit;

begin;create type city_record as(city_name text);create type
state_record as (state_name text, population bigint,cities
city_record[]);create type country_record as (country_name text,
leader text, states state_record[]);commit;

then array_transport.
create or replacefunction array_transport (all_items anyarray)
returns setof text
returns null on null inputlanguage plpgsql as
$body$declare
item record;begin
foreach item in array all_items
loop
return next(to_json(item)::text);
end loop;end;
$body$;

finally the main function.
create or replace function country_select_json (_country_id bigint)
returns country_record[]
as
$$
declare
_result text;

begin
select array_agg(single_item)
from (select
array_agg(row(
co.name,
co.leader,
(select array_agg(row
(s.name,
s.population,
(select array_agg
(row

(c.name)::city_record)

from cities c

join states s using (state_id)

where s.country_id = co.country_id)

)::state_record) from states s where s.country_id = co.country_id
)
)::country_record)
as single_item
from country co
where co.country_id = _country_id)y into _result;--
raise info 'state_record test: %', _result;
return (_result);
end
$$ language plpgsql;
--------------------------------------------------------------------------------------------------------
Run select * from array_transport(country_select_json(1));
what I got:

{"country_name":"India","leader":"Narendra
Modi","states":[{"state_name":"Karnataka","population":64100000,"cities":[{"city_name":"Mumbai"},{"city_name":"Pune"}]},{"state_name":"Rajasthan","population":68900000,"cities":[{"city_name":"Mumbai"},{"city_name":"Pune"}]},{"state_name":"Maharashtra","population":125700000,"cities":[{"city_name":"Mumbai"},{"city_name":"Pune"}]}]}
(1 row)

*Expected*

{"country_name":"India","leader":"Narendra
Modi","states":[{"state_name":"Karnataka","population":64100000"},{"state_name":"Rajasthan","population":68900000}},{"state_name":"Maharashtra","population":125700000,"cities":[{"city_name":"Mumbai"},{"city_name":"Pune"}]}]}
(1 row)