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!
Best Answer
What about:
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:
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: