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:
>

Another option that just occurred to me is a new function (say, to_json) that converts the parameter to its json representation, with type json.

Then this would be possible:

select array_to_json(ARRAY[to_json('foo'), to_json(100), to_json(true)]);

-Greg