On Sat, Aug 11, 2012 at 7:05 AM, jan zimmek <jan.zimmek@web.de> wrote:
> hi,
>
> i am looking into json support of the upcoming 9.2 release and have a question about the row_to_json function. is
therea 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".
select
row_to_json(t1)
from (
select
'joe' as username,
(select project from (values(1, 'prj1')) as project(project_id,
project_name)) as project
) t1;
merlin