Postgresql nested row_to_json not working

Question

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!

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;