I need a query in postgres that returns the length of arrays but without taking empty values into consideration

so given the table:

id     | names
===============
1        {John, , Wayne}
2        {Luke, Harold, }
3        {Bill}
4        {Will, , }

They don’t have a standard and some values may come empty ( for example {Will, , }). I tried:

SELECT array_length(names, 1)
FROM nameTable

But I get this:

names
======
3
3
1
3

and I want it to return:

names
======
2
2
1
1

So I need something which gives me the length only of the populated fields (empty spaces like ‘ ‘) shouldn’t be counted.

Answer

You can remove the NULL values and then count:

array_length(array_remove(names, NULL), 1)

For one-dimensional arrays, I find that cardinality() is convenient:

cardinality(array_remove(names, NULL))