add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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