pg数据库-创建 json

使用PostgreSQL 9.4.24 (Greenplum Database 6.3.0 build dev)云数据库版本。

Json 生成器

1. 使用 row_to_json把行数据转成 json，单行结果集转换
2. 使用 json_agg
   1. 将SQL查询结果集转成 json，多行结果集转换。可转成多个结果集，也可以转换成一个结果集
   2. 还可以配合子查询，做多层次的 json 结构
3. 使用 json_build_object()，自定义创建无序 json 结构{}
4. 使用 json_build_array()，创建一个有序 json 结构[]

--1. 单行转json
select row_to_json(pub_area_latest) from dim.pub_area_latest where area_id = '440000';

--2.1 多行转一个json
select json_agg(pub_area_latest) from dim.pub_area_latest where province_id = '440000';

--2.2 嵌套多级 json
with shiji as (select distinct city_id, city_name as countys
               from dim.pub_area_latest
               where area_id = city_id and city_id <> province_id),
     shenji as (select a.province_id, a.province_name, json_agg(b) as citys
                from dim.pub_area_latest a
                         join shiji b on a.area_id = b.city_id
                group by 1, 2)
select json_agg(shenji)
from shenji;

--3. 自定义无序json结构
select json_build_object(
   'paramId', 1,
   'paramName', 1,
   'paramLevel', 1,
   'subAmount', 1
);

--4. 自定义有序json结构
select  json_build_array(
            '{"area_id" : "411100", "area_name" : "漯河市", "firstPy" : "", "level" : 2, "type" : 1}'::json,
            '{"area_id" : "411100", "area_name" : "漯河市", "firstPy" : "", "level" : 2, "type" : 1}'::json,
            '{"area_id" : "411100", "area_name" : "漯河市", "firstPy" : "", "level" : 2, "type" : 1}'::json
)

业务场景

源表是数仓中行政区划维表（此表扁平化4层结构，空值使用最低级别值回填），要根据源表写两个函数，实现如下功能。函数的出参必须是 json 格式。

功能 1，根据区划编码，获取子集编码。比如，输入320000区划编码，输出江苏省所有市级数据

/*
    根据 area_id 获取子集 1
*/
create or replace function dim.get_area_subset_by_id(p_area_id varchar) returns json
    LANGUAGE plpgsql AS
$$
DECLARE
    result        json;
    p_param_level int;
    p_param_name  varchar;
    p_sub_count   int;
    sub_areas     json;
begin
    --根据 area_id 获取对应的名称和等级
    select case
               when area_id = province_id then 1
               when area_id = city_id then 2
               when area_id = county_id then 3
               when area_id = street_id then 4
               else 0 end      as level,
           case
               when area_id = province_id then province_name
               when area_id = city_id then city_name
               when area_id = county_id then county_name
               when area_id = street_id then street_name
               else '未知' end as name
    into p_param_level, p_param_name
    from dim.pub_area_latest
    where area_id = p_area_id;

    --获取子集
    p_sub_count := 0;
    if p_param_level is null then
        result := json_build_object(
                'message', '未收录该区划编码',
                'paramId', p_area_id
            );
        return result;
    elsif p_param_level = 0 then
        result := json_build_object(
                'message', '无法识别编码等级',
                'paramId', p_area_id
            );
        return result;
    elsif p_param_level = 1 then
        --获取二级
        with tp as (select distinct city_id                    as area_id,
                                    city_name                  as area_name,
                                    dim.cnfirstchar(city_name) as firstPy,
                                    p_param_level + 1          as level
                    from dim.pub_area_latest
                    where province_id = p_area_id
                      and area_id <> p_area_id)
        select json_agg(tp), count(*)
        into sub_areas, p_sub_count
        from tp;
    elsif p_param_level = 2 then
        --获取三级
        with tp as (select distinct county_id                    as area_id,
                                    county_name                  as area_name,
                                    dim.cnfirstchar(county_name) as firstPy,
                                    p_param_level + 1            as level
                    from dim.pub_area_latest
                    where city_id = p_area_id
                      and area_id <> p_area_id)
        select json_agg(tp), count(*)
        into sub_areas, p_sub_count
        from tp;
    elsif p_param_level = 3 then
        --获取四级
        with tp as (select distinct street_id                    as area_id,
                                    street_name                  as area_name,
                                    dim.cnfirstchar(street_name) as firstPy,
                                    p_param_level + 1            as level
                    from dim.pub_area_latest
                    where county_id = p_area_id
                      and area_id <> p_area_id)
        select json_agg(tp), count(*)
        into sub_areas, p_sub_count
        from tp;
    elsif p_param_level = 4 then
        result := json_build_object(
                'message', '街道级暂无子集',
                'paramId', p_area_id
            );
        return result;
    end if;

    result := json_build_object(
            'paramId', p_area_id,
            'paramName', p_param_name,
            'paramLevel', p_param_level,
            'subAmount', p_sub_count,
            'subSet', sub_areas
        );

    RETURN result;
END
$$;

功能 2，根据区划等级，获取所有子集编码。一级二级三级分别对应省、市、区。

比如

输入一级，获取省所有数据；

输入二级，获取省市所有数据；

输入三级，获取省市区所有数据；

create or replace function dim.get_area_subset_by_level(p_level integer) returns json
    LANGUAGE plpgsql AS
$$
DECLARE
    result      json;
    sub_areas   json;
BEGIN
    if p_level = 1 then
        --获取所有一级
        with tp as (select distinct province_id                    as area_id,
                                    province_name                  as area_name,
                                    dim.cnfirstchar(province_name) as firstPy,
                                    1                              as level
                    from dim.pub_area_latest
                    where province_id = area_id)
        select json_agg(tp)
        into sub_areas
        from tp;
    elsif p_level = 2 then
        --获取所有二级
        with shiji as (select distinct city_id, city_name, dim.cnfirstchar(city_name) as firstPy, 2 as level
                       from dim.pub_area_latest
                       where area_id = city_id
                         and city_id <> province_id),
             shenji as (select a.province_id,
                               a.province_name,
                               dim.cnfirstchar(province_name) as firstPy,
                               1                              as level,
                               json_agg(b)                    as citys
                        from dim.pub_area_latest a
                                 join shiji b on a.area_id = b.city_id
                        group by 1, 2)
        select json_agg(shenji)
        into sub_areas
        from shenji;
    elsif p_level = 3 then
        --获取所有三级
        with quji as (select distinct county_id, county_name, dim.cnfirstchar(county_name) as firstPy, 3 as level
                      from dim.pub_area_latest
                      where county_id = area_id
                        and county_id <> city_id),
             shiji as (select city_id,
                              city_name,
                              dim.cnfirstchar(city_name) as firstPy,
                              2                          as level,
                              json_agg(b)                as countys
                       from dim.pub_area_latest a
                                join quji b on a.area_id = b.county_id
                       group by city_id, city_name),
             shenji as (select a.province_id,
                               a.province_name,
                               dim.cnfirstchar(province_name) as firstPy,
                               1                              as level,
                               json_agg(b)                    as citys
                        from dim.pub_area_latest a
                                 join shiji b on a.area_id = b.city_id
                        group by 1, 2)
        select json_agg(shenji)
        into sub_areas
        from shenji;
    else
        result := json_build_object(
                'message', '暂时只支持 1，2，3级',
                'p_level', p_level
            );
        return result;
    end if;

    result := json_build_object(
            'paramLevel', p_level,
            'subSet', sub_areas
        );

    RETURN result;
END
$$;

参考文章：

德哥博客（PG 问题看德哥博客基本都能解决）、Json 函数操作符