json support for composites in upcoming 9.2

From: jan zimmek <jan(dot)zimmek(at)web(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: json support for composites in upcoming 9.2
Date: 2012-08-11 12:05:06
Message-ID: 3AE10649-9C99-4B01-AA94-431824DC2DB3@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi,

i am looking into json support of the upcoming 9.2 release and have a question about the row_to_json function. is there a way to specify the column aliases of a nested composite row without creating a custom type ?

here is an example:

select
row_to_json(t1)
from (
select
'joe' as username,
row(1, 'prj1') project
) t1;

the output is:

{"username":"joe","project":{"f1":1,"f2":"prj1"}}

i want the output to be:

{"username":"joe","project":{"project_id":1,"project_name":"prj1"}}

i thought of something like this:

select
row_to_json(t1)
from (
select
'joe' as username,
(select * from (values(1, 'prj1')) as project(project_id, project_name)) as project
) t1;

but this fails with "ERROR: subquery must return only one column".

any help would be really appreciated.

regards
jan zimmek

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2012-08-11 13:17:44 Re: External Storage of data dir
Previous Message Laszlo Fogas 2012-08-11 07:33:29 Re: slowness what only full vacuum can solve