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 t (
response int,
document int,
label text,
bill int,
product json,
answer text
);
INSERT INTO t VALUES
(71788176, 79907201, 'bill', 26899, null, null),
(71788176, 79907201, 'amount', null, null, '1'),
(71788176, 79907201, 'product', null, '{"name": "Shoes"}', null),
(71788176, 79907201, 'price', null, null, '25.99');
SELECT * FROM t
4 rows affected
| response | document | label | bill | product | answer |
|---|---|---|---|---|---|
| 71788176 | 79907201 | bill | 26899 | null | null |
| 71788176 | 79907201 | amount | null | null | 1 |
| 71788176 | 79907201 | product | null | {"name": "Shoes"} | null |
| 71788176 | 79907201 | price | null | null | 25.99 |
-- Variant 1: compare entire json string text-based
SELECT
response,
document,
MAX(bill) FILTER (WHERE label = 'bill') as bill,
MAX(answer) FILTER (WHERE label = 'amount') as amount,
MAX(product::text) FILTER (WHERE label = 'product') as product,
MAX(answer) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document
| response | document | bill | amount | product | price |
|---|---|---|---|---|---|
| 71788176 | 79907201 | 26899 | 1 | {"name": "Shoes"} | 25.99 |
-- Varirant 2: Read the value of the "name" attribute
SELECT
response,
document,
MAX(bill) FILTER (WHERE label = 'bill') as bill,
MAX(answer) FILTER (WHERE label = 'amount') as amount,
MAX(product ->> 'name') FILTER (WHERE label = 'product') as product,
MAX(answer) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document
| response | document | bill | amount | product | price |
|---|---|---|---|---|---|
| 71788176 | 79907201 | 26899 | 1 | Shoes | 25.99 |