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(dot)zlatev(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +