-2

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.