The following bug has been logged on the website:
Bug reference: 8533
Logged by: Mike
Email address: m@pplcast.com
PostgreSQL version: 9.3.1
Operating system: all
Description:
Not sure if bug or feature, but the query below without the "offset 0" fails
with "ERROR: cannot extract field from a non-object", while the one with
succeeds as intended. Please excuse the messiness.
=# explain select aaa->'bbb'->'ccc'->'ddd'->>1, aaa->'bbb'->'ccc' from
(select aaa from yyy where not json_isarray(aaa->'bbb'->'ccc')) _(aaa) where
(aaa->'bbb'->'ccc'->'ddd'->>1) = 'eee';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on yyy (cost=0.00..3502.00 rows=32 width=32)
Filter: ((((((aaa -> 'bbb'::text) -> 'ccc'::text) -> 'ddd'::text) ->> 1)
= 'eee'::text) AND (NOT json_isarray(((aaa -> 'bbb'::text) ->
'ccc'::text))))
(2 rows)
=# explain select aaa->'bbb'->'ccc'->'ddd'->>1, aaa->'bbb'->'ccc' from
(select aaa from yyy where not json_isarray(aaa->'bbb'->'ccc') offset 0)
_(aaa) where (aaa->'bbb'->'ccc'->'ddd'->>1) = 'eee';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on _ (cost=0.00..3526.31 rows=32 width=32)
Filter: (((((_.aaa -> 'bbb'::text) -> 'ccc'::text) -> 'ddd'::text) ->> 1)
= 'eee'::text)
-> Seq Scan on yyy (cost=0.00..3379.92 rows=6485 width=32)
Filter: (NOT json_isarray(((aaa -> 'bbb'::text) -> 'ccc'::text)))
(4 rows)
CREATE OR REPLACE FUNCTION json_isarray(obj json) RETURNS bool AS $$
BEGIN
PERFORM json_array_elements(obj);
RETURN true;
EXCEPTION WHEN invalid_parameter_value THEN
RETURN false;
END;
$$ LANGUAGE plpgsql IMMUTABLE;