clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
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. 497232 fiddles created (9460 in the last week).

CREATE TABLE products ( product_id serial PRIMARY KEY , title text not null -- more columns omitted ); CREATE TABLE order_items ( order_item_id serial PRIMARY KEY , product_id integer REFERENCES products , attributes jsonb -- more columns omitted ); INSERT INTO products(product_id, title) VALUES (1, 'prod1') , (2, 'prod2'); INSERT INTO order_items (product_id, attributes) VALUES (1, '[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]') , (1, '[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]') , (1, '[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]') -- added row to show effects of missing / additional keys , (2, '[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)", "foo": 123}, {"value": "Kırmızı"}]');
2 rows affected
4 rows affected
 hidden batch(es)


SELECT oi.sayi, oi.attributes , a.* FROM ( SELECT attributes, COUNT(attributes) AS sayi FROM order_items GROUP BY 1 ) oi LEFT JOIN LATERAL jsonb_to_recordset(attributes) a(name text, value text) ON true;
sayi attributes name value
1 [{"foo": 123, "name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"value": "Kırmızı"}] Kampanya Seçimi USB Çakmaklık (7,99 TL)
1 [{"foo": 123, "name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"value": "Kırmızı"}] Kırmızı
3 [{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}] Kampanya Seçimi USB Çakmaklık (7,99 TL)
3 [{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}] İp, Dikiş Rengi Kırmızı
 hidden batch(es)