By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test (
id integer
, questionid text
, questionanswer text
);
INSERT INTO test (id,questionid,questionanswer) values
(1,'[101,102,103]','[["option_11"],["Test message 1"],["option_14"]]')
, (2,'[201]','[["option_3","option_4"]]')
, (3,'[301,302]','[["option_1","option_3"],["option_1"]]')
, (4,'[976,1791,978,1793,980,1795,982,1797]','[["option_2","option_3","option_4","option_5"],["Test message"],["option_4"],["Test message2"],["option_2"],["Test message3"],["option_2","option_3"],["Test message4"]]')
;
CREATE TABLE
INSERT 0 4
SELECT t.id, qa.q_id
, json_array_elements_text(qa.answers) AS answer
FROM test t
CROSS JOIN LATERAL (
SELECT *
FROM json_array_elements_text(t.questionid::json) WITH ORDINALITY q(q_id, ord)
JOIN json_array_elements(t.questionanswer::json) WITH ORDINALITY a(answers, ord) USING (ord)
) qa
ORDER BY t.id, qa.ord;
| id | q_id | answer |
|---|---|---|
| 1 | 101 | option_11 |
| 1 | 102 | Test message 1 |
| 1 | 103 | option_14 |
| 2 | 201 | option_3 |
| 2 | 201 | option_4 |
| 3 | 301 | option_1 |
| 3 | 301 | option_3 |
| 3 | 302 | option_1 |
| 4 | 976 | option_2 |
| 4 | 976 | option_3 |
| 4 | 976 | option_4 |
| 4 | 976 | option_5 |
| 4 | 1791 | Test message |
| 4 | 978 | option_4 |
| 4 | 1793 | Test message2 |
| 4 | 980 | option_2 |
| 4 | 1795 | Test message3 |
| 4 | 982 | option_2 |
| 4 | 982 | option_3 |
| 4 | 1797 | Test message4 |
SELECT 20