Skip to content

Instantly share code, notes, and snippets.

@pstef

pstef/Postgres-json-output.txt

Last active October 21, 2018 07:58
Show Gist options
  • Save pstef/b62742eec70a49c0b9a6c1dfce91c218 to your computer and use it in GitHub Desktop.
Save pstef/b62742eec70a49c0b9a6c1dfce91c218 to your computer and use it in GitHub Desktop.
Download ZIP
Showing of Postgres's features
Raw
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}] │
└────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment