clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555483 fiddles created (37458 in the last week).

CREATE TABLE product_type( id INTEGER NOT NULL, title TEXT NOT NULL );
 hidden batch(es)


CREATE TABLE property( id INTEGER NOT NULL, type_id INTEGER NOT NULL, title TEXT NOT NULL );
 hidden batch(es)


CREATE TABLE value( id INTEGER NOT NULL, property_id INTEGER NOT NULL, value JSONB NOT NULL DEFAULT '{}' );
 hidden batch(es)


INSERT INTO product_type(id, title) VALUES (1, 'Тельавизор'), (2, 'Плазма'), (3, 'Монитор')
3 rows affected
 hidden batch(es)


INSERT INTO property(id, type_id, title) VALUES (1, 1, 'Диагональ'), (2, 1, 'Тип вещания'), (3, 1, 'Звук'), (4, 2, 'Диагональ'), (5, 2, 'Разрешение'), (6, 2, 'Звук'), (7, 3, 'Диагональ'), (8, 3, 'Разрешение'), (9, 3, 'Разъемы')
9 rows affected
 hidden batch(es)


INSERT INTO value(id, property_id, value) VALUES (1, 1, '{"type": "float", "unit": "cm", "value": "88см"}'), (1, 2, '{"type": "string", "unit": "", "value": "NTSC"}'), (1, 3, '{"type": "string", "unit": "", "value": ["mono", "stereo"]}'), (1, 4, '{"type": "int", "unit": "in", "value": "65"}'), (1, 5, '{"type": "string", "unit": "", "value": "1920x1080"}'), (1, 6, '{"type": "string", "unit": "", "value": "5:1"}'), (1, 7, '{"type": "float", "unit": "in", "value": "13.3"}'), (1, 8, '{"type": "string", "unit": "", "value": "1366x768"}'), (1, 9, '{"type": "string", "unit": "", "value": ["HDMIx2", "DVI", "MiniDP"]}')
9 rows affected
 hidden batch(es)


SELECT pt.title, p.title, v.value -> 'value' AS value_list FROM product_type AS pt LEFT JOIN property AS p ON p.type_id = pt.id LEFT JOIN value AS v ON v.property_id = p.id
title title value_list
Тельавизор Диагональ "88см"
Тельавизор Тип вещания "NTSC"
Тельавизор Звук ["mono", "stereo"]
Плазма Диагональ "65"
Плазма Разрешение "1920x1080"
Плазма Звук "5:1"
Монитор Диагональ "13.3"
Монитор Разрешение "1366x768"
Монитор Разъемы ["HDMIx2", "DVI", "MiniDP"]
 hidden batch(es)