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 stagework (
work_id int4 NULL,
authors jsonb NULL
);
INSERT INTO stagework(work_id, authors) VALUES
(1, '["Kafka, Franz", "Feynman, Richard", "Pratchett, Terry"]'),
(2, '["Tolkien, JRR", "Tolkien, C"]');
CREATE TABLE
INSERT 0 2
SELECT s.work_id, a.author
FROM stagework s
CROSS JOIN jsonb_array_elements_text(s.authors) AS a(author);
| work_id | author |
|---|---|
| 1 | Kafka, Franz |
| 1 | Feynman, Richard |
| 1 | Pratchett, Terry |
| 2 | Tolkien, JRR |
| 2 | Tolkien, C |
SELECT 5
SELECT s.work_id, a.author
FROM stagework s, jsonb_array_elements_text(s.authors) AS a(author);
| work_id | author |
|---|---|
| 1 | Kafka, Franz |
| 1 | Feynman, Richard |
| 1 | Pratchett, Terry |
| 2 | Tolkien, JRR |
| 2 | Tolkien, C |
SELECT 5