Thread: BUG #8218: Error when querying an JSON data, 9.3beta

The following bug has been logged on the website:

Bug reference:      8218
Logged by:          Daniel Zlatev
Email address:      daniel.zlatev@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Windows
Description:        =


Dears,

today I was playing with some JSON data, and I have noticed something
strange.

1. I've created the follwing table:
CREATE TABLE products (
    data JSON
);

2. Then inserting some data:
INSERT INTO products(data) VALUES('{"id": 1, "name": "shoes", "in_stock":
5}');
INSERT INTO products(data) VALUES('[1,2,3,4,5]');

3. Then i've run this SELECT statement
SELECT * FROM products WHERE (data->>'in_stock')::integer > 0

Output was:
[Err] ERROR: cannot extract field from a non-object

I can understand the reason behind this error(JSON array don't has fields),
but for me it is very logical postgres to exclude this row from the
returning set, rather to throw an error.

So that is it.

Best regards
Daniel

daniel.zlatev@gmail.com writes:
> CREATE TABLE products (
>     data JSON
> );
> INSERT INTO products(data) VALUES('{"id": 1, "name": "shoes", "in_stock": 5}');
> INSERT INTO products(data) VALUES('[1,2,3,4,5]');
> SELECT * FROM products WHERE (data->>'in_stock')::integer > 0

> Output was:
> [Err] ERROR: cannot extract field from a non-object

> I can understand the reason behind this error(JSON array don't has fields),
> but for me it is very logical postgres to exclude this row from the
> returning set, rather to throw an error.

Hm.  In principle we could allow ->> to return NULL rather than failing
when there's no such field, but I'm not sure that would represent good
language design.  However, this example definitely shows there are some
holes in the current set of JSON manipulation functions.  The only way
to avoid a failure here would be to write something like

    WHERE (CASE WHEN json_has_field(data, 'in_stock') THEN
           (data->>'in_stock')::integer ELSE NULL::integer END) > 0

but there is no "json_has_field" test function, nor any nice way to
build one from the provided functions.

It's probably too late to address this for 9.3, but we ought to put it
on the to-do list for 9.4.

            regards, tom lane

On Sun, Jun  9, 2013 at 01:24:26PM -0400, Tom Lane wrote:
> daniel.zlatev@gmail.com writes:
> > CREATE TABLE products (
> >     data JSON
> > );
> > INSERT INTO products(data) VALUES('{"id": 1, "name": "shoes", "in_stock": 5}');
> > INSERT INTO products(data) VALUES('[1,2,3,4,5]');
> > SELECT * FROM products WHERE (data->>'in_stock')::integer > 0
>
> > Output was:
> > [Err] ERROR: cannot extract field from a non-object
>
> > I can understand the reason behind this error(JSON array don't has fields),
> > but for me it is very logical postgres to exclude this row from the
> > returning set, rather to throw an error.
>
> Hm.  In principle we could allow ->> to return NULL rather than failing
> when there's no such field, but I'm not sure that would represent good
> language design.  However, this example definitely shows there are some
> holes in the current set of JSON manipulation functions.  The only way
> to avoid a failure here would be to write something like
>
>     WHERE (CASE WHEN json_has_field(data, 'in_stock') THEN
>            (data->>'in_stock')::integer ELSE NULL::integer END) > 0
>
> but there is no "json_has_field" test function, nor any nice way to
> build one from the provided functions.
>
> It's probably too late to address this for 9.3, but we ought to put it
> on the to-do list for 9.4.

Was this addressed for 9.4 because I don't see it?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

On 01/23/2014 10:05 PM, Bruce Momjian wrote:
> On Sun, Jun  9, 2013 at 01:24:26PM -0400, Tom Lane wrote:
>> daniel.zlatev@gmail.com writes:
>>> CREATE TABLE products (
>>>      data JSON
>>> );
>>> INSERT INTO products(data) VALUES('{"id": 1, "name": "shoes", "in_stock": 5}');
>>> INSERT INTO products(data) VALUES('[1,2,3,4,5]');
>>> SELECT * FROM products WHERE (data->>'in_stock')::integer > 0
>>> Output was:
>>> [Err] ERROR: cannot extract field from a non-object
>>> I can understand the reason behind this error(JSON array don't has fields),
>>> but for me it is very logical postgres to exclude this row from the
>>> returning set, rather to throw an error.
>> Hm.  In principle we could allow ->> to return NULL rather than failing
>> when there's no such field, but I'm not sure that would represent good
>> language design.  However, this example definitely shows there are some
>> holes in the current set of JSON manipulation functions.  The only way
>> to avoid a failure here would be to write something like
>>
>>     WHERE (CASE WHEN json_has_field(data, 'in_stock') THEN
>>            (data->>'in_stock')::integer ELSE NULL::integer END) > 0
>>
>> but there is no "json_has_field" test function, nor any nice way to
>> build one from the provided functions.
>>
>> It's probably too late to address this for 9.3, but we ought to put it
>> on the to-do list for 9.4.
> Was this addressed for 9.4 because I don't see it?
>

9.4 does have json_typeof(), which should let you construct an adequate
test along the lines Tom suggests.

cheers

andrew

On Thu, Jan 23, 2014 at 11:32:25PM -0500, Andrew Dunstan wrote:
>
> On 01/23/2014 10:05 PM, Bruce Momjian wrote:
> >On Sun, Jun  9, 2013 at 01:24:26PM -0400, Tom Lane wrote:
> >>daniel.zlatev@gmail.com writes:
> >>>CREATE TABLE products (
> >>>     data JSON
> >>>);
> >>>INSERT INTO products(data) VALUES('{"id": 1, "name": "shoes", "in_stock": 5}');
> >>>INSERT INTO products(data) VALUES('[1,2,3,4,5]');
> >>>SELECT * FROM products WHERE (data->>'in_stock')::integer > 0
> >>>Output was:
> >>>[Err] ERROR: cannot extract field from a non-object
> >>>I can understand the reason behind this error(JSON array don't has fields),
> >>>but for me it is very logical postgres to exclude this row from the
> >>>returning set, rather to throw an error.
> >>Hm.  In principle we could allow ->> to return NULL rather than failing
> >>when there's no such field, but I'm not sure that would represent good
> >>language design.  However, this example definitely shows there are some
> >>holes in the current set of JSON manipulation functions.  The only way
> >>to avoid a failure here would be to write something like
> >>
> >>    WHERE (CASE WHEN json_has_field(data, 'in_stock') THEN
> >>           (data->>'in_stock')::integer ELSE NULL::integer END) > 0
> >>
> >>but there is no "json_has_field" test function, nor any nice way to
> >>build one from the provided functions.
> >>
> >>It's probably too late to address this for 9.3, but we ought to put it
> >>on the to-do list for 9.4.
> >Was this addressed for 9.4 because I don't see it?
> >
>
> 9.4 does have json_typeof(), which should let you construct an
> adequate test along the lines Tom suggests.

Ah, I didn't catch that.  Thank you!

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +