PostgreSQL技术问题：将JSON数组行转换为整数行并插入BRAVO表

在PostgreSQL中遇到技术瓶颈，无法可视化数据导致操作推进困难。此前使用Snowflake但已无权限，刚接触PostgreSQL，因操作复杂度和插入逻辑问题无法清晰查看数据。

参考其他帖子写出的查询返回空结果，推测问题出在TWO子查询中，具体表结构和查询如下：

表结构 #

表ALPHA（空表） #

CREATE TABLE ALPHA (
  X_ID SERIAL PRIMARY KEY,
  X_JSON JSON
);

表CHARLIE（已填充数据） #

CREATE TABLE CHARLIE (
  Y_ID INT NOT NULL PRIMARY KEY,
  NAME TEXT NOT NULL
);

INSERT INTO CHARLIE (Y_ID, NAME)
VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I'),(10,'J'),(11,'K'),(12,'L');

表BRAVO（空中间表） #

CREATE TABLE BRAVO (
  X_ID INT NOT NULL REFERENCES ALPHA (X_ID),
  Y_ID INT NOT NULL REFERENCES CHARLIE (Y_ID)
);

当前查询语句（返回空结果） #

WITH ONE AS (
  INSERT INTO ALPHA (X_ID, X_JSON)
  VALUES (1, '{"a": [4,5,6]}'),
         (2, '{"a": [7,8,9]}'),
         (3, '{"a": [10,11,12]}')
  RETURNING X_ID, JSON_EXTRACT_PATH(X_JSON, 'a') AS Y_ID
), TWO AS (
  SELECT ALPHA.X_ID,
         (SELECT * FROM JSON_TO_RECORDSET(ONE.Y_ID -> 'rows') AS list(columns int)) AS Y_ID_A
  FROM ALPHA
    JOIN ONE ON ALPHA.X_ID = ONE.X_ID
)
INSERT INTO BRAVO (X_ID, Y_ID)
SELECT X_ID,
       Y_ID_A
FROM TWO
RETURNING *

期望结果 #

需要将数据转换为如下格式并插入到BRAVO表中：

原查询的错误点 #

1. JSON路径错误：原TWO子查询中使用ONE.Y_ID -> 'rows'，但ONE.Y_ID提取的是{"a": [4,5,6]}中的数组[4,5,6]，不存在rows键，这会返回NULL，导致后续无数据。
2. JSON函数使用错误：JSON_TO_RECORDSET用于解析JSON对象数组（如[{"col":1},{"col":2}]），但此处是普通数值数组，应使用json_array_elements来拆分数组元素。
3. 冗余关联：ONE子查询已经返回了插入的X_ID和对应的JSON数组，无需再关联ALPHA表，反而可能因ALPHA表在CTE执行时还未完成写入导致关联失败。

修正后的查询语句 #

WITH ONE AS (
  INSERT INTO ALPHA (X_ID, X_JSON)
  VALUES (1, '{"a": [4,5,6]}'),
         (2, '{"a": [7,8,9]}'),
         (3, '{"a": [10,11,12]}')
  RETURNING X_ID, X_JSON
)
INSERT INTO BRAVO (X_ID, Y_ID)
SELECT 
  ONE.X_ID,
  (json_array_elements(X_JSON -> 'a'))::int AS Y_ID
FROM ONE
RETURNING *;

说明 #

• 从ONE子查询直接返回X_ID和原始X_JSON，避免提前提取数组导致后续处理麻烦。
• 使用json_array_elements(X_JSON -> 'a')拆分JSON数组中的每个数值元素，再通过::int转换为整数类型，匹配BRAVO表的Y_ID字段类型。
• 无需额外的TWO子查询，直接在INSERT的SELECT语句中完成数组拆分，逻辑更简洁，避免冗余关联导致的问题。

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