Postgres Pro
Downloads
Home   >   mailing lists

Thread: json support for composites in upcoming 9.2

json support for composites in upcoming 9.2

From
jan zimmek
Date:
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
away 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

Re: json support for composites in upcoming 9.2

From
Merlin Moncure
Date:
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

Re: json support for composites in upcoming 9.2

From
jan zimmek
Date:
big thanks, this is exactly what i was looking for.

jan

Am 13.08.2012 um 17:58 schrieb Merlin Moncure <mmoncure@gmail.com>:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general