I've hit a roadblock that I can't seem to get past as I'm not able to visualize the data. I'm fairly new to Postgres (more experienced with Snowflake, but no longer have access), but due to the complexity and inserts I'm not able to visualize the data.
Looking at other posts, the closest I was able to get is the following (which only presents a blank response which I believe has something to do with the TWO subquery, but am otherwise lost):
Table ALPHA (Empty table)
CREATE TABLE ALPHA (
X_ID SERIAL PRIMARY KEY,
X_JSON JSON
);
Table CHARLIE (Filled table)
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');
Table BRAVO (Empty intermediary table)
CREATE TABLE BRAVO (
X_ID INT NOT NULL REFERENCES ALPHA (X_ID),
Y_ID INT NOT NULL REFERENCES CHARLIE (Y_ID)
);
Query:
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 *
What I need the output to look like so I can insert it into table BRAVO:
| X_ID | Y_ID |
|---|---|
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 2 | 7 |
| 2 | 8 |
| 2 | 9 |
| 3 | 10 |
| 3 | 11 |
| 3 | 12 |
You can use json_array_elements to expands a JSON array to a set of rows :
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->'a' AS Y_ID_A
),
TWO AS (
SELECT o.X_ID, v.value::text::int AS Y_ID
FROM ONE o
CROSS JOIN LATERAL json_array_elements (o.Y_ID_A) v
)
INSERT INTO BRAVO (X_ID, Y_ID)
SELECT X_ID,
Y_ID
FROM TWO