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

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.

regards, tom lane