BUG #7620: array_to_json doesn't support heterogeneous arrays

On Tue, Oct 23, 2012 at 6:32 PM, <ghazel@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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.

So, it's not possible to form a heterogeneous json array, when this is often
desired.

This is not really a bug because the feature is working as intended.
Postgres arrays are homogonous so what you're asking really isn't
possible. You can though use row_to_json to work around:

select row_to_json(row('foo', 100, true));

merlin

On Oct 23, 2012, at 6:03 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Oct 23, 2012 at 6:32 PM, <ghazel@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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.

So, it's not possible to form a heterogeneous json array, when this is often
desired.

This is not really a bug because the feature is working as intended.
Postgres arrays are homogonous so what you're asking really isn't
possible. You can though use row_to_json to work around:

select row_to_json(row('foo', 100, true));

That doesn't produce the same results.

Call it a feature request or a bug report, the postgres json support fails to make the json I need.

-Greg

On Tue, Oct 23, 2012 at 8:05 PM, Greg Hazel <ghazel@gmail.com> wrote:

On Oct 23, 2012, at 6:03 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Oct 23, 2012 at 6:32 PM, <ghazel@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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.

So, it's not possible to form a heterogeneous json array, when this is often
desired.

This is not really a bug because the feature is working as intended.
Postgres arrays are homogonous so what you're asking really isn't
possible. You can though use row_to_json to work around:

select row_to_json(row('foo', 100, true));

That doesn't produce the same results.

Call it a feature request or a bug report, the postgres json support fails to make the json I need.

I didn't say that it did: what it does is return a javascript object
which is only a very little bit different from an array. For example,
you can do jquery each() over either. I guess if you had to have an
array, you could do it like this:

array_to_json(ARRAY['foo'::text, 100::text, true::text])

merlin

On Oct 23, 2012, at 6:17 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Oct 23, 2012 at 8:05 PM, Greg Hazel <ghazel@gmail.com> wrote:

On Oct 23, 2012, at 6:03 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Oct 23, 2012 at 6:32 PM, <ghazel@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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.

So, it's not possible to form a heterogeneous json array, when this is often
desired.

This is not really a bug because the feature is working as intended.
Postgres arrays are homogonous so what you're asking really isn't
possible. You can though use row_to_json to work around:

select row_to_json(row('foo', 100, true));

That doesn't produce the same results.

Call it a feature request or a bug report, the postgres json support fails to make the json I need.

I didn't say that it did: what it does is return a javascript object
which is only a very little bit different from an array. For example,
you can do jquery each() over either. I guess if you had to have an
array, you could do it like this:

array_to_json(ARRAY['foo'::text, 100::text, true::text])

Still not exactly the same json, since 100 and true would be quoted strings. I'm not parsing it with jQuery, it's a client with type expectations.

-Greg

On 10/24/2012 07:32 AM, ghazel@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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

On Oct 24, 2012, at 12:21 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 10/24/2012 07:32 AM, ghazel@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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

On Wed, Oct 24, 2012 at 2:21 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 10/24/2012 07:32 AM, ghazel@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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?

That's an interesting idea, but I'd like to see the OP make a
convincing case why the data must be returned as an array. In
javascript there isn't much difference...but maybe there's an
important point I'm missing.

merlin

On Oct 24, 2012, at 12:21 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 10/24/2012 07:32 AM, ghazel@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 7620
Logged by: Greg Hazel
Email address: ghazel@gmail.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

On 10/24/2012 11:46 PM, Greg Hazel wrote:

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)]);

That's been repeatedly discussed (and requested, and had patches posted)
on -hackers. Right now the standing opinion appears to be that "json"
represents a whole json document, and json documents must be arrays or
JavaScript objects, not scalars, so it is not valid to provide a scalar
"to_json".

I posted patches to support this functionality - as did several others
as it turns out - and they've all been rejected.

--
Craig Ringer