CodeProject

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.

First off, lets’ 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 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 an 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

CodeProject
Git Subtree Merge –The Quick Version
ASP.Net
ASP.NET MVC: Keep Private Settings Out of Source Control
ASP.Net
Reverse-Engineering an Existing Database in your ASP.NET MVC Application Using Entity Framework Model/Database-First
  • Jason

    JasonJason

    Author Reply

    Why are you wrapping SELECTs?
    I cannot see the advantage, nor the difference, in doing

    select json_agg(t.*) from mytable t where somecol=25;

    vs

    select json_agg(t.*) from (select * from mytable where somecol=25) t ;

    I tried it; both produce the same result and use the same plan.


  • Michael Jay

    I was preparing to write this all in my Node environment. I’m so glad I decided to perform a quick search while I waited for my kettle to boil. Tea saves the day again… also you helped. Ha!


  • Miguel

    MiguelMiguel

    Author Reply

    Select whatever from (select * from table) ? REALLY? That kind of makes me think this page is useless.


    • John Atten

      John AttenJohn Atten

      Author Reply

      Yes, REALLY, if your intent is to aggregate the results of the select into json using Postgres’ JSON functions. Whether such use is the best way to achieve what you need is a different question. In my particular case, it was. But do feel free to share your obviously superior knowledge with us.


  • Wale

    WaleWale

    Author Reply

    Hey, thanks for this post. Immediately solved my issue on getting a JSON back from a referenced table


  • Michael Barton

    Thanks for your article. It would be great if you could post an SQL fiddle for the data you use, then I could play around and repeat or modify the queries you’re generating.