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

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?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +