Re: BUG #7620: array_to_json doesn't support heterogeneous arrays

On Oct 24, 2012, at 12:21 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

> On 10/24/2012 07:32 AM, ghazel(at)gmail(dot)com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 7620
>> Logged by: Greg Hazel
>> Email address: ghazel(at)gmail(dot)com
>> PostgreSQL version: 9.2.1
>> Operating system: Amazon Linux
>> Description:
>>
>> array_to_json(ARRAY['foo', 100, true]) complains because arrays can't have
>> mixed types, but json arrays can.
>
> The issue here isn't array_to_json, it's PostgreSQL arrays.
>
> What you appear to want is a way to call row_to_json so that it produces
> a json array instead of a json object as it currently does. That way you
> could pass it a ROW() construct, composite type, or record, and have it
> output a heterogeneous JSON array.
>
> This isn't a bug, but it's a perfectly reasonable feature request if
> re-interpreted a little. It will never work with PostgreSQL arrays,
> though, because the arrays themselves cannot contain mixed types:
>
> regress=# SELECT ARRAY[1,'test'];
> ERROR: invalid input syntax for integer: "test"
> LINE 1: SELECT ARRAY[1,'test'];
> ^
> Instead you want a way to take this:
>
> regress=# SELECT ROW(1,'test');
> row
> ----------
> (1,test)
> (1 row)
>
> and output the json:
>
> [1,"test"]
>
> instead of a json object:
>
> regress=# SELECT row_to_json(ROW(1,'test'));
> row_to_json
> ----------------------
> {"f1":1,"f2":"test"}
> (1 row)
>
>
> Would a version of `row_to_json` that output a json array satisfy your
> needs?

Sure, that would be fine.

-Greg