如何将PostgreSQL的JSONB列转换为数组列（多边形点场景）

针对你需要把JSONB列里的点数组转成((x1,y1),(x2,y2),...)格式数组列的需求，这里提供两种实用方法，分别对应PostgreSQL原生空间点数组类型和纯文本格式：

方法1：生成原生point[]类型数组（推荐） #

这种方法生成的是PostgreSQL内置的point类型数组，支持后续空间运算，比纯文本格式更实用。

步骤1：添加目标数组列 #

先给表新增一个point[]类型的列：

ALTER TABLE your_table ADD COLUMN position_array point[];

步骤2：更新数据到新列 #

使用JSONB解析函数和数组聚合函数完成转换：

UPDATE your_table
SET position_array = (
    -- 展开JSONB数组，逐个构造point并聚合为数组
    SELECT array_agg(point((elem->>'x')::numeric, (elem->>'y')::numeric))
    FROM jsonb_array_elements(position) AS elem
);

处理空值/空数组的健壮版本 #

如果你的position列可能存在null或空数组[]，可以用CASE语句兼容：

UPDATE your_table
SET position_array = CASE
    WHEN position IS NULL OR position = '[]'::jsonb THEN '{}'::point[]
    ELSE (
        SELECT array_agg(point((elem->>'x')::numeric, (elem->>'y')::numeric))
        FROM jsonb_array_elements(position) AS elem
    )
END;

方法2：生成纯文本格式的数组字符串 #

如果你确实需要纯文本格式的((455, -3165),(455, 50), (5708, 50))，可以用字符串拼接的方式：

步骤1：添加文本列 #

ALTER TABLE your_table ADD COLUMN position_array text;

步骤2：更新数据 #

UPDATE your_table
SET position_array = CASE
    WHEN position IS NULL OR position = '[]'::jsonb THEN '()'
    ELSE '(' || string_agg('(' || elem->>'x' || ', ' || elem->>'y' || ')', ', ') || ')'
END
FROM jsonb_array_elements(position) AS elem;

关键函数说明 #

• jsonb_array_elements(position)：将JSONB数组拆分为多行，每行对应一个{x,y}对象
• point(x, y)：PostgreSQL内置函数，将两个数值构造成一个point类型
• array_agg()：将多行数据聚合为一个数组
• string_agg()：将多行字符串按指定分隔符拼接成一个字符串

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