Showing of Postgres's features

Postgres-json-output.txt

WITH
manufacturers (name, headquarters) AS (VALUES
  ('BMW', 'Munich'), ('Toyota', 'Toyota'), ('Fiat', 'Turin')
),
models (manufacturer, name, year) AS (VALUES
  ('BMW', 'm4', 2000), ('BMW', 'm5', 2000),
  ('Toyota', 'corolla', 2000), ('Toyota', 'yaris', 2000),
  ('Fiat', 'panda', 2000), ('Fiat', 'uno', 2000)
),
products (manufacturer, model, color, quantity, pretax) AS (VALUES
  ('BMW', 'm4', 'black', 3, 12345678),
  ('BMW', 'm4', 'pink', 2, 23456789),
  ('BMW', 'm5', 'black', 1, 43215678),
  ('Toyota', 'corolla', 'black', 0, 32415867),
  ('Toyota', 'yaris', 'black', 3, 8765432),
  ('Toyota', 'yaris', 'red', 0, 1112223),
  ('Fiat', 'panda', 'pink', 1, 3332221),
  ('Fiat', 'uno', 'white', 1, 8822334),
  ('Fiat', 'uno', 'black', 1, 45362718)
)
SELECT
  mf.name,
  json_agg(json_build_object('model', p.model, 'color', p.color)) AS all_products,
  json_agg(essential_info.*) FILTER (WHERE p.quantity > 0) AS available_products
FROM manufacturers mf
JOIN models m ON m.manufacturer = mf.name
JOIN products p ON p.model = m.name
CROSS JOIN LATERAL (SELECT p.model, p.color, p.quantity, p.pretax, p.pretax * 1.20 AS posttax) essential_info
GROUP BY mf.name;

┌─[ RECORD 1 ]───────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ name               │ Fiat                                                                                                                     │
│ all_products       │ [{"model" : "panda", "color" : "pink"}, {"model" : "uno", "color" : "white"}, {"model" : "uno", "color" : "black"}]      │
│ available_products │ [{"model":"panda","color":"pink","quantity":1,"pretax":3332221,"posttax":3998665.20},                                   ↵│
│                    │  {"model":"uno","color":"white","quantity":1,"pretax":8822334,"posttax":10586800.80},                                   ↵│
│                    │  {"model":"uno","color":"black","quantity":1,"pretax":45362718,"posttax":54435261.60}]                                   │
├─[ RECORD 2 ]───────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ name               │ Toyota                                                                                                                   │
│ all_products       │ [{"model" : "corolla", "color" : "black"}, {"model" : "yaris", "color" : "black"}, {"model" : "yaris", "color" : "red"}] │
│ available_products │ [{"model":"yaris","color":"black","quantity":3,"pretax":8765432,"posttax":10518518.40}]                                  │
├─[ RECORD 3 ]───────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ name               │ BMW                                                                                                                      │
│ all_products       │ [{"model" : "m4", "color" : "black"}, {"model" : "m4", "color" : "pink"}, {"model" : "m5", "color" : "black"}]           │
│ available_products │ [{"model":"m4","color":"black","quantity":3,"pretax":12345678,"posttax":14814813.60},                                   ↵│
│                    │  {"model":"m4","color":"pink","quantity":2,"pretax":23456789,"posttax":28148146.80},                                    ↵│
│                    │  {"model":"m5","color":"black","quantity":1,"pretax":43215678,"posttax":51858813.60}]                                    │
└────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘