Use Postgres JSON Type and Aggregate Functions to Map Relational Data to JSON

Postgres is just too cool. Because Postgres can work with both JSON and arrays as first-class data types, it is possible to perform some very handy mappings on the server end which would become potential performance bottlenecks on the client side.

When working in Node.JS, it might be handy to do some heavy lifting via Postgres to reduce some n+1 / lazy loading issues, especially when pulling data for display. Using some of Postgres’ in-built JSON functions in conjunction with the JSON data type, we can compose ready-to-use JSON objects before returning the result.

In this post, we’ll take a quick look at the row_to_json() function, the array_to_json() function, and the json_agg() function, and see how between these three we can shape relational data on the database side, possibly in a much more performant manner than we might within our application.

• Transform Row Data to a JSON Object Using row_to_json()
• Aggregate Rows into a JSON Array Using the json_agg() Function
• Aggregate Parent and Child Records into a singe JSON Object in the Result Set

First off, let's aggregate some row data into a JSON object.

Example Data from Chinook (modified)

For the examples which follow, I will be using a slightly modified version of the Chinook database, which I tweaked to be a little more Postgres-friendly.

Consider the following tables from Chinook (with Sample Data added):

The Chinook Artists and Albums Table

CREATE TABLE albums
(
    id int DEFAULT nextval('albums_id_seq'::regclass) NOT NULL,
    title VARCHAR(160) NOT NULL,
    artist_id INT NOT NULL,
    CONSTRAINT pk_albums PRIMARY KEY  (id)
);
CREATE TABLE artists
(
    id int DEFAULT nextval('artists_id_seq'::regclass) NOT NULL,
    name VARCHAR(120),
    CONSTRAINT pk_artists PRIMARY KEY  (id)
);

For our purposes, the artists and albums tables, populated as they are, will do admirably.

Transform Row Data to a JSON Object Using row_to_json()

We can use the Postgres row_to_json() function in a straightforward manner to take a full row of data, and squeeze it into a JSON object before the result set is returned.

For example, let’s grab all the artist records, and return rows of JSON:

Transform Artist Records to JSON

select row_to_json(artists)
from (
  select * from artists
) as artists

Running the query above gives output like so:

Result Set from row_to_json on the Artists Table

{"id":1,"name":"AC/DC"}
{"id":2,"name":"Accept"}
{"id":3,"name":"Aerosmith"}
// ... a bunch more artist records ...

We could modify the above, and add some criteria so that we are limiting the records to a specific artist like so:

Transform a Specific Artist Record to JSON

select row_to_json(artists)
from (
  select * from artists where id = 12
) as artists

Running the above with criteria set to artist id 12 (which happens to be Black Sabbath) returns the following predictable result:

Result Set from row_to_json() with Criteria

{"id":12,"name":"Black Sabbath"} 

Now, each artist can have any number of albums in the albums table. Wouldn’t it be nice if we could grab the albums for a specific artist, and transform them into JSON array?

Aggregate Rows into a JSON Array Using the json_agg() Function

Postgres offers us the json_agg() function, which takes input values and aggregates them as a JSON array.

For example, we might want to aggregate the album records for a specific artist into a JSON array:

Aggregate Album Records into JSON Array using json_agg()

select json_agg(albums)
from (
  select * from albums where artist_id = 12
) as albums;

Running this query returns the following:

Result Set from json_agg() on the Albums Table

[{"id":16,"title":"Black Sabbath","artist_id":12}, 
 {"id":17,"title":"Black Sabbath Vol. 4 (Remaster)","artist_id":12}]

There we go… we now have an array of albums for a specific artist. Now, can we expand on this, and return the artist record as a JSON object, including an albums property represented by such an array, containing all the albums for each artist?

Why, yes, yes we can!

Aggregate Parent and Child Records into a singe JSON Object in the Result Set

Let’s return each row as a complete JSON object, such that each artist record is represented as JSON, and contains an albums property which itself contains an array of album objects.

We can aggregate the album records for each artist using json_agg() in a correlated subquery with the artists table, and then we can pass each row to the row_to_json() function to transform the result into complete JSON object, ready for use in our application.

Per usual with correlated subqueries, keep your eye on the numerous database object aliases here. Naming can get a little funky…

Transform and Aggregate Artist and Album Records into JSON Objects

select row_to_json(art) as artists
from(
  select a.id, a.name, 
  (select json_agg(alb)
  from (
    select * from albums where artist_id = a.id
  ) alb
) as albums
from artists as a) art;

The result of the query above:

Result Set from Correlated Transform/Aggregate Query

{"id":1,"name":"AC/DC","albums":[
   {"id":1,"title":"For Those About To Rock We Salute You","artist_id":1}, +
   {"id":4,"title":"Let There Be Rock","artist_id":1}
 ]}
 {"id":2,"name":"Accept","albums":[
   {"id":2,"title":"Balls to the Wall","artist_id":2},                    +
   {"id":3,"title":"Restless and Wild","artist_id":2}
 ]}
 {"id":3,"name":"Aerosmith","albums":[
   {"id":5,"title":"Big Ones","artist_id":3}
 ]}
 // ... a bunch more records ...

Voila – we have ready-to-use JSON to return to our application. Most importantly, we have completely assembled artist objects, with albums available as an array via the albums property on each artist.

The Tip of the Iceberg

Postgres offers a wealth of innovative features such as the JSON / JSONB data type, the array data type, HStore, and plenty of functionality built around each. What we have seen here is a simple solution to one of the common challenges presented by data access and managing parent/child relationships – the lazy loading / n+1 problem.

By flexing the power of Postgres’ JSON type and associated functions, we have blurred the line between document and relational storage.

If you haven’t taken Postgres for a spin yet, I strongly suggest you do. There’s more where that came from.

Additional Resources and Items of Interest

• Installing and Configuring PostgreSQL 9.4 on Linux Mint/Ubuntu
• A More Useful Port of the Chinook Database to Postgresql
• Installing and Using SQLite on Windows
• Biggy: A Fresh Look at High-Performance, Synchronized In-Memory Persistence for .NET

John on GoogleCodeProject