Plpgsql一次输入将所有相关字段/列获取到json失败 [英] plpgsql one input get all the related fields/columns to json failed

问题描述

尝试回答this问题，然后发现我无法解决它。

基本思想：传播两次，从Country_id传播到State_id，State_id传播到City_id两次，然后需要连接Country_id两次。当我们在州级别执行array_agg时，我们需要显式Join Country_id，在城市级别我们也需要使用Join Country_id。

参考链接：https://github.com/hettie-d/NORM/tree/master/sql
基本思路，输入一个国家_id，所有相关的国家、州、市级信息都会转换成json格式。

---

准备。我使用Country_id、State_id、City_id，因为它们更具描述性。

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;

---

数组传输

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$;

--主函数COUNTRY_SELECT_JSON

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;

运行
select * from array_transport(country_select_json(1));

 {"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)
   

国家一级，州一级可以，但城市一级是错的。如何解决这个问题。
预期结果：

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

---

更新2022-03-04。

(select array_agg
(c.name) as city_name
from cities c
join states s using (state_id)
where s.country_id = co.country_id)

现在我知道问题了：因为传播首先是从城市，然后是州，然后是乡村。一旦函数输入country_id，则所有与国家相关的城市名称将被拉到一起。

推荐答案

此查询是否满足您的需求？(Result here)

with ci as (select cities.state_id,jsonb_agg(jsonb_build_object('city_name',cities.name)) as cities from cities group by state_id)
select jsonb_pretty(jsonb_build_object(
    'country_name',c.name,
    'leader',c.leader,
    'states', jsonb_agg(jsonb_build_object(
        'state_name',s.name,
        'population',s.population,
        'cities',ci.cities
    ))
))
from country c left join states s on s.country_id = c.country_id 
left join ci on ci.state_id = s.state_id
where c.country_id = 1
group by c.name,c.leader

// Result
{
"country_name": "India",
"leader": "Narendra Modi",
"states": [
    {
        "state_name": "Maharashtra",
        "population": 125700000,
        "cities": [
            {
                "city_name": "Mumbai"
            },
            {
                "city_name": "Pune"
            }
        ]
    },
    {
        "state_name": "Rajasthan",
        "population": 68900000,
        "cities": null
    },
    {
        "state_name": "Karnataka",
        "population": 64100000,
        "cities": null
    }
]

这篇关于Plpgsql一次输入将所有相关字段/列获取到json失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！