如何在PostgreSQL JSON响应中添加ST_Centroid结果？报错排查

错误原因 #

1. 最外层查询result里直接引用了center.longitude，但center这个CTE并没有关联到result的FROM子句中——你只在子查询t里做了CROSS JOIN center c，但t的SELECT列表没把center的坐标字段带出来，外层根本拿不到center表的字段。
2. 就算能拿到，用json_agg也是错误的：center是单行结果（所有符合条件的点的质心只有一个），聚合后会生成包含多个重复对象的数组，和你期望的单个center对象结构不符。

修正后的SQL #

WITH center AS (
  SELECT
    ST_X(centroid) AS longitude,
    ST_Y(centroid) AS latitude
  FROM (
    SELECT st_centroid(
      st_transform(
        st_collect(
          array(
            SELECT location::geometry FROM meetup
            WHERE st_dwithin(location, st_point(yyy.yyyyyy, xx.xxxxxx), 10000)
          )
        ), 4326)
    ) AS centroid
  ) AS subquery
)
select row_to_json(result) from (
  select 
    count(*) as total,
    json_build_object('longitude', CAST(c.longitude AS TEXT), 'latitude', CAST(c.latitude AS TEXT)) as center,
    array_to_json(array_agg(row_to_json(t)), true) as data 
  from (
    select 
      m.id, 
      m.name,
      st_y(location::geometry) as lat, 
      st_x(location::geometry) as long, 
      st_distance(location, st_point(yyy.yyyyyy, xx.xxxxxx)::geography) as dist_in_km
    from public.meetup as m
    where st_dwithin(location, st_point(yyy.yyyyyy, xx.xxxxxx)::geography, 10000.0)
      AND m.start_at > current_timestamp
    order by location <-> st_point(yyy.yyyyyy, xx.xxxxxx)::geography
  ) t
  CROSS JOIN center c -- 在外层关联center，因为它是单行结果
) result;

关键修改点 #

• 修正了center CTE里的重复计算：去掉了多余的st_centroid(centroid)，直接用ST_X(centroid)和ST_Y(centroid)取坐标。
• 把CROSS JOIN center从子查询t移到了外层result的FROM子句中，这样外层能直接访问center的字段。
• 替换json_agg为json_build_object：因为center是单行数据，直接生成单个JSON对象，符合你期望的结构。
• 子查询t里不再需要关联center，避免了不必要的重复数据。

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