NORM(NO ORM) transform two one-many relationships into a single json. - Mailing list pgsql-sql
| From | Jian He |
|---|---|
| Subject | NORM(NO ORM) transform two one-many relationships into a single json. |
| Date | |
| Msg-id | CAMV54g1sEZo6QrgHe5BkaqOXigDyk5GbrUEZUJLG2EV0+EuSzw@mail.gmail.com Whole thread Raw |
| List | pgsql-sql |
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
Reference link: https://github.com/hettie-d/NORM/tree/master/sql
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 replace
function array_transport (all_items anyarray) returns setof text returns null on null input
language 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)