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!