PostgreSQL bug with JSON type?

Question

This query not working, but the error not make sense

SELECT count(*) FROM t 
WHERE json_typeof(info->'vatids')='array' AND json_array_length(info->'vatids')>1;

ERROR: cannot get array length of a scalar

PS: idem with JSONb.

Answer 1

You didn't show us the data that is in that column nor did you show us the table definition. But I guess that you have something like this:

create table t (info json);
insert into t 
  (info)
values
  ('{"vatids" : ["one"]}'),
  ('{"vatids" : ["one", "two"]}'), 
  ('{"vatids" : "n/a"}');

There is no "short-circuit" of conditions in SQL, all conditions in the WHERE clause are applied (logically) at the same time, so json_array_length(info->'vatids') will fail for the third row even though you have json_typeof(info->'vatids')='array' as well.

The following will also not work, because the optimizer is smart enough to push the condition into the derived table:

select *
from (
  select info
  from t 
  where json_typeof(info->'vatids') = 'array' 
) x
where json_array_length(info->'vatids') > 1;

In this case you need a CTE that completely filters out the invalid rows, only then you can apply the condition on the array length:

with only_arrays as (
  select info
  from t 
  where json_typeof(info->'vatids') = 'array' 
)
select count(*)
from only_arrays
where json_array_length(info->'vatids') > 1;

This works because CTEs in Postgres act as an optimization fence and thus conditions from the outer query are not pushed into the CTE. Most of the times this is an disadvantage, but in this case it is indeed helpful.

Answer 2

CTE doesn't seem to work anymore from version 15 of postgresql at least.

A solution could be :

select *
from (
  select  case when jsonb_typeof(info->'vatids') = 'array' then 
    jsonb_array_length(info->'vatids') else 0 end as len, t.*
 from t ) x
 where len > 1;