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

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. +