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.
jsonpostgresql
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.
Best Answer
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:
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 havejson_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:
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:
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.