PostgreSQL中如何将JSONB数组连续值对转换为几何线？

核心解决方案 #

直接通过带索引的JSONB数组拆解+分组配对，生成点数组后调用ST_MakeLine生成线几何：

SELECT
  ST_MakeLine(ARRAY_AGG(ST_MakePoint(x, y) ORDER BY grp)) AS line_geom
FROM (
  SELECT
    -- 用ctid标识每行数据，有主键的话替换成主键字段（比如id）更可靠
    ctid,
    -- 每两个元素为一组，grp值相同的为一对坐标
    (idx - 1) / 2 AS grp,
    -- 提取每组的第一个值作为x坐标（对应数组第1、3、5...个元素）
    MAX(CASE WHEN idx % 2 = 1 THEN val END) AS x,
    -- 提取每组的第二个值作为y坐标（对应数组第2、4、6...个元素）
    MAX(CASE WHEN idx % 2 = 0 THEN val END) AS y
  FROM (
    SELECT
      ctid,
      -- 将JSONB元素转为整数
      (elem::text)::int AS val,
      -- 保留数组元素的原始索引（从1开始）
      idx
    FROM mytable,
         jsonb_array_elements_with_index(data -> 'foo') WITH ORDINALITY AS arr(elem, idx)
  ) AS elements
  GROUP BY ctid, grp
) AS point_pairs
GROUP BY ctid;

关键细节说明 #

• 顺序保留：用jsonb_array_elements_with_index替代普通的jsonb_array_elements，这个函数会返回元素在原数组中的索引，确保配对过程严格遵循数组的原始顺序，不需要额外排序。
• 配对逻辑：通过(idx - 1)/2将相邻的两个索引归为同一组（比如索引1、2→grp0，3、4→grp1），再用CASE语句分别提取每组的x、y值。
• 异常处理：如果数组长度为奇数，最后一个无法配对的元素会被自动忽略，若需处理这种场景，可以在子查询中添加过滤条件，比如只处理偶数位的元素：WHERE idx <= (SELECT jsonb_array_length(data->'foo') FROM mytable WHERE ctid = elements.ctid) - (jsonb_array_length(data->'foo') % 2)。

对现有代码的扩展 #

你之前的代码仅提取了数组元素，现在通过引入带索引的拆解和分组逻辑，就能实现数值配对，最终生成符合要求的LineString几何对象。

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