Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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!

share|improve this question
1  
I'm thinking about making the subobjects first and then plug them into their final place. Not sure this can work yet. –  dezso Jan 18 '13 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.) –  Chris Travers Jan 22 '13 at 6:18
add comment

1 Answer

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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