Unpivoting data using JSON functions

Most of our use-cases for the built-in json support in PostgreSQL is not to implement schemaless design storage, but instead to remold data. Remolding can take the form of restructuring data into json documents suitable for web maps, javascript charting web apps, or datagrids. It also has uses beyond just outputting data in json form. In addition the functions are useful for unraveling json data into a more meaningful relational form.

One of the common cases we use json support is what we call UNPIVOTING data. We demonstrated this in Postgres Vision 2018 presentation in slide 23. This trick won't work in other relational databases that support JSON because it also uses a long existing feature of PostgreSQL to be able to treat a row as a data field.

Unpivoting data is the process of converting columns to rows. It's the reverse of a cross tabluation (aka PIVOT). So why would you ever need to unpivot data?

Take for example if you had a spreadsheet where your HR person decided to input each employees hours in columns where each column represents a separate week.

The spreadsheet data might look something like this:

Employee2018-05-042018-05-112018-05-182018-05-25
Regina70403565
Leo50804565
Caitlin40253535
Jane20251518

If you are using a spreadsheet as your data entry and presentation tool, then this is a great setup, but for storing the data in a relational database and being able to do arbitrary date summary aggregations, it's easier to work with the data in a format such as below because it's more flexible how you rollup date ranges and you don't have to add a new column to your table every time a new week comes along.

EmployeePPEhours
Regina2018-05-0470
Regina2018-05-1140
Regina2018-05-1835
Regina2018-05-1865

Note this trick you can accomplish using the hstore extension as well as we covered a while back in Unpivoting data in PostgreSQL. Since JSON/JSONB functions are built-in no CREATE EXTENSION is required to use them.

INSERT INTO staff_hours(employee, ppe, hours)
SELECT hours_raw."Employee" As employee,  j.key::date AS ppe,  j.val::numeric As hours
FROM hours_raw, LATERAL jsonb_each_text(to_jsonb(hours_raw)) AS j(key,val)
WHERE j.key NOT IN('Employee');

The above query is composed for 4 steps