PostgreSQL：如何查询array_agg生成的二维数组指定元素？

核心原因分析 #

你遇到的path[1]返回NULL的问题，大概率是两种情况：

• 对应分组下没有聚合到任何坐标点，array_agg生成了空数组；
• 对数组元素的访问逻辑未匹配PostgreSQL的数组类型规则。

正确的元素访问方式 #

array_agg(array[c.lat, c.lng])生成的是元素为一维数组的一维数组（类型为double precision[]，而非严格意义的二维数组），直接使用下标即可访问其中的单个[纬度,经度]数组：

1. 获取指定位置的路径点

比如获取第1个或第3个路径点：

with t as (
    select 
        influence_region_id,
        ir.city_id, 
        array[c2.lat, c2.lng] as center_latlng,
        array_agg(array[c.lat, c.lng] order by "index") as path
    from influence_regions_coordinates irc 
    join coordinates c 
        on c.id = irc.coordinate_id
    join influence_regions ir 
        on ir.id = irc.influence_region_id
    join coordinates c2 
        on c2.id = ir.center 
    group by influence_region_id, ir.city_id, center_latlng
)
select 
    influence_region_id,
    city_id,
    center_latlng,
    path[1] as first_path_point, -- 第一个[纬度,经度]数组
    path[3] as third_path_point  -- 第三个[纬度,经度]数组
from t
where array_length(path, 1) > 0; -- 过滤无路径点的记录

2. 展开所有路径点

如果需要逐个获取所有路径点，正确使用unnest的方式如下：

with t as (
    select 
        influence_region_id,
        ir.city_id, 
        array[c2.lat, c2.lng] as center_latlng,
        array_agg(array[c.lat, c.lng] order by "index") as path
    from influence_regions_coordinates irc 
    join coordinates c 
        on c.id = irc.coordinate_id
    join influence_regions ir 
        on ir.id = irc.influence_region_id
    join coordinates c2 
        on c2.id = ir.center 
    group by influence_region_id, ir.city_id, center_latlng
)
select 
    influence_region_id,
    city_id,
    center_latlng,
    unnest(path) as path_point -- 逐个输出每个[纬度,经度]数组
from t;

3. 单独提取路径点的纬度/经度

如果需要从指定路径点中拆分出纬度或经度，只需对路径点数组再次使用下标：

with t as (
    select 
        influence_region_id,
        ir.city_id, 
        array[c2.lat, c2.lng] as center_latlng,
        array_agg(array[c.lat, c.lng] order by "index") as path
    from influence_regions_coordinates irc 
    join coordinates c 
        on c.id = irc.coordinate_id
    join influence_regions ir 
        on ir.id = irc.influence_region_id
    join coordinates c2 
        on c2.id = ir.center 
    group by influence_region_id, ir.city_id, center_latlng
)
select 
    influence_region_id,
    city_id,
    (path[1])[1] as first_lat, -- 第一个路径点的纬度
    (path[1])[2] as first_lng  -- 第一个路径点的经度
from t
where array_length(path, 1) > 0;

验证数组是否为空 #

如果path[1]返回NULL，先检查对应分组的路径数组长度：

with t as (
    select 
        influence_region_id,
        array_agg(array[c.lat, c.lng] order by "index") as path
    from influence_regions_coordinates irc 
    join coordinates c 
        on c.id = irc.coordinate_id
    group by influence_region_id
)
select 
    influence_region_id,
    array_length(path, 1) as path_length -- 数组长度为0说明无路径点
from t;

内容的提问来源于stack exchange，提问作者Alexandre Krabbe