This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like the following:

  { "type": "add"
    , "id":"285947086237736960"
    , "version" : 1
    , "lang":"en"
    ,"fields":  {
        "id" : "285947086237736960"
        , "created_at":"2013-01-01"
        , "content":"bla bla blah LOL ROFL"
  }, ...

The nested "fields" attribute comes from a db table called "tweets". Both id's come from the same table. However, type, version, lang are always the same. I've looked at the suggestions from https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins, which mentions using aliased subquery expressions. That didn't work at all, I still get the f1, f2, etc. The suggestions from Set names to attributes when creating JSON with row_to_json works for a single level, but I can't get it to work for the nested "fields" attribute.

As far as I can tell, this will only work with a with statement similar to:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(tweets) from tweets;

But, how do I get the nested "fields"? Any help is greatly appreciated!

  • 1
    I'm thinking about making the subobjects first and then plug them into their final place. Not sure this can work yet. Jan 18, 2013 at 10:32
  • 1
    @dezso, I think that's right. Of course keep in mind there are wonderful aggregates for this, like array_agg, and the like, that do in fact work on tuples. The key is to get the data structure right first, and then convert it. (Speaking as someone who takes way too much advantage of what Pg can do regarding complex types.) Jan 22, 2013 at 6:18

1 Answer 1


What about:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately. Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp, 

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray. You could also do:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select array_to_json(array_agg(tweets)) from tweets;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.