Re: row_to_json question

On Sat, Jun 23, 2012 at 5:15 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>> How can I use row_to_json for a subset of columns in a row? (without
>> creating a new view or using a CTE?)
>>
>> What I want returned:
>> {"email_address":"joe(at)tanga(dot)com","username":"joevandyk"}
>> Note that there is no "id" column in the result.
>>
>>
>> create table users (id serial primary key, email_address varchar,
>> username varchar);
>> insert into users (email_address, username) values ('joe(at)tanga(dot)com',
>> 'joevandyk');
>
> This is the best I can come up with:
>
> select row_to_json(f) from (select email_address, username from users) f;
>    {"email_address":"joe(at)tanga(dot)com","username":"joevandyk"}
>
> Is there a cleaner way to do this?

you do it like this (it avoids the subquery):
select row_to_json(row(a,b)) from foo;

unfortunately this will anonymize the fields to 'f1, f2', etc in the
JSON. you can avoid that via composite type:
create type foo_t (a int, b text);
select row_to_json(row(a,b)::foo_t) from foo;

merlin