Обсуждение: row_to_json question

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@tanga.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@tanga.com',
'joevandyk');

select row_to_json(users) from users;
 {"id":1,"email_address":"joe@tanga.com","username":"joevandyk"}
      Correct, except that the "id" column is in the result.


select row_to_json(row(users.email_address, users.username)) from users;
 {"f1":"joe@tanga.com","f2":"joevandyk"}
       The column names are incorrect.

On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk <joe@tanga.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@tanga.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@tanga.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@tanga.com","username":"joevandyk"}

Is there a cleaner way to do this?

On Sat, Jun 23, 2012 at 5:15 PM, Joe Van Dyk <joe@tanga.com> wrote:
> On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk <joe@tanga.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@tanga.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@tanga.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@tanga.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