I've been using the new JSON functionality in Postgres a lot recently: I'm fond of saying that Postgresql is the best NoSQL database available today. I'm quite serious about this: having used key-value and JSON stores such as CouchDB in the past, it's amazing to me how the Postgres developers have managed to marry the best of traditional relational technology with the flexibility of schema-free JSON documents.

As of version 9.3, postgres allows you to create JSON column types, and provides a number of functions to access and iterate through the data stored in them.

This week I discovered another hidden gem - json_agg() . This function lets you take the results from an aggregation operation and convert them into a JSON block - very helpful if you're then going to work with the returned data in a language like Python

To demonstrate this, we'll first set up some simple tables.

%load_ext sql
%config SqlMagic.feedback=False

%%sql
postgresql://testuser:password@localhost/test

u'Connected: testuser@test'

%%sql
CREATE TABLE IF NOT EXISTS person (
    name text primary key
);

INSERT INTO person (name) VALUES
('emily'),('arthur'),('nicki'),('oliver')
;

[]

We can query this in the usual way:

%sql SELECT * FROM person;

But we can also use json_agg()

%sql SELECT json_agg(name) FROM person

Which gives us a single object to work with. So far, this isn't particularly helpful, but it becomes very useful when we start doing JOINS

%%sql
CREATE TABLE IF NOT EXISTS action(
    id serial primary key,
    created timestamp with time zone default now(),
    person_name text references person,
    type text not null
);

INSERT INTO action(person_name, type) VALUES ('emily','login');
INSERT INTO action(person_name, type) VALUES ('emily','pageview');
INSERT INTO action(person_name, type) VALUES ('arthur','login');
INSERT INTO action(person_name, type) VALUES ('emily','logout');
INSERT INTO action(person_name, type) VALUES ('nicki','password_change');
INSERT INTO action(person_name, type) VALUES ('nicki','createpost');

[]

If we want to ask Postgres to give us every user and every action they've performed, we could do it this way:

%sql SELECT person.name,  action.type , action.created FROM action JOIN person ON action.person_name=person.name

But then iterating through this recordset is a pain - I can't easily construct a nested for loop to iterate through each person and then through each action.

Enter json_agg()

%sql SELECT person.name,  json_agg(action) FROM action JOIN person ON action.person_name=person.name GROUP BY person.name

Which becomes much more usable in Python:

people = %sql SELECT person.name,  json_agg(action) FROM action JOIN person ON action.person_name=person.name GROUP BY person.name

for name, actions in people:
    print name

arthur
emily
nicki

for name, actions in people:
    print name
    for action in actions:
        print '\t',action['type']

arthur
    login
emily
    login
    pageview
    logout
nicki
    password_change
    createpost

So now we've managed to easily convert relational Postgres data into a hierarchical Python data structure. From here we can easily continue to XML, JSON, HTML or whatever document type suits your need.