Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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