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

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.

cheers

andrew