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

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?

--
Craig Ringer