Unpivoting data using JSONB   

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.