One of my favorite uses of JSONB functionality is to unpivot data.
Let's say you were handed a dataset with this structure where each type of bed column holds the count of units of that kind.
CREATE TABLE housing_data (
neighborhood VARCHAR(50),
studio INTEGER,
br1 INTEGER,
br2 INTEGER,
br3 INTEGER,
br4 INTEGER
);
INSERT INTO housing_data (neighborhood, studio, br1, br2, br3, br4)
VALUES ('Downtown', 500, 800, 1000, 1200, 1500),
('Midtown', 700, 950, 1150, 1400, 1700),
('Uptown', 600, 800, 1000, 1200, 1500),
('Suburbs', 300, 450, 600, 750, 900);
For your reporting needs, you would like each bed type count to be a separate row. Here is one of my favorite tricks for accomplishing this:
SELECT neighborhood, replace(je.key,'br','') AS bed_type, je.value::integer AS num_units
FROM housing_data AS d
CROSS JOIN jsonb_each_text( to_jsonb(d) - 'neighborhood') AS je;
The assumption here is that every column except for neighborhood is a bedroom unit count. The output is:
neighborhood | bed_type | num_units --------------+----------+----------- Downtown | 1 | 800 Downtown | 2 | 1000 Downtown | 3 | 1200 Downtown | 4 | 1500 Downtown | studio | 500 Midtown | 1 | 950 Midtown | 2 | 1150 Midtown | 3 | 1400 Midtown | 4 | 1700 Midtown | studio | 700 Uptown | 1 | 800 Uptown | 2 | 1000 Uptown | 3 | 1200 Uptown | 4 | 1500 Uptown | studio | 600 Suburbs | 1 | 450 Suburbs | 2 | 600 Suburbs | 3 | 750 Suburbs | 4 | 900 Suburbs | studio | 300 (20 rows) (20 rows)
If you had more than one column that wasn't a bedroom count you could revise your subtraction, to exclude that too using revised: to_jsonb(d) - 'neighborhood' - 'other_column', alternatively you could use array construct to_jsonb(d) - ARRAY['neighborhood','other_column']. This trick can also be accomplished using the hstore extension and converting a row to an hstore and using the each function of hstore.