row_to_json question

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: row_to_json question
Date: 2012-06-23 22:03:42
Message-ID: CACfv+p+UjdKz2s-oAEGJHmNcHxxm=poncXdcEY_tVbU-JnDKnQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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');

select row_to_json(users) from users;
{"id":1,"email_address":"joe(at)tanga(dot)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(at)tanga(dot)com","f2":"joevandyk"}
The column names are incorrect.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Van Dyk 2012-06-23 22:15:57 Re: row_to_json question
Previous Message Jasen Betts 2012-06-23 12:18:40 Re: Feature discussion: Should syntax errors abort a transaction?