Thread: BUG #7620: array_to_json doesn't support heterogeneous arrays
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.
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 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 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 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 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:
>>=20
>> Bug reference: 7620
>> Logged by: Greg Hazel
>> Email address: ghazel@gmail.com
>> PostgreSQL version: 9.2.1
>> Operating system: Amazon Linux
>> Description:
>>=20
>> array_to_json(ARRAY['foo', 100, true]) complains because arrays can't =
have
>> mixed types, but json arrays can.
>>=20
>> So, it's not possible to form a heterogeneous json array, when this =
is often
>> desired.
>=20
> 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:
>=20
> 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 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 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:
>>=20
>>> On Tue, Oct 23, 2012 at 6:32 PM, <ghazel@gmail.com> wrote:
>>>> The following bug has been logged on the website:
>>>>=20
>>>> Bug reference: 7620
>>>> Logged by: Greg Hazel
>>>> Email address: ghazel@gmail.com
>>>> PostgreSQL version: 9.2.1
>>>> Operating system: Amazon Linux
>>>> Description:
>>>>=20
>>>> array_to_json(ARRAY['foo', 100, true]) complains because arrays =
can't have
>>>> mixed types, but json arrays can.
>>>>=20
>>>> So, it's not possible to form a heterogeneous json array, when this =
is often
>>>> desired.
>>>=20
>>> 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:
>>>=20
>>> select row_to_json(row('foo', 100, true));
>>=20
>> That doesn't produce the same results.
>>=20
>> Call it a feature request or a bug report, the postgres json support =
fails to make the json I need.
>=20
> 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:
>=20
> 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 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:
>>=20
>> Bug reference: 7620
>> Logged by: Greg Hazel
>> Email address: ghazel@gmail.com
>> PostgreSQL version: 9.2.1
>> Operating system: Amazon Linux
>> Description: =20
>>=20
>> array_to_json(ARRAY['foo', 100, true]) complains because arrays can't =
have
>> mixed types, but json arrays can.
>=20
> The issue here isn't array_to_json, it's PostgreSQL arrays.
>=20
> 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.
>=20
> 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:
>=20
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