How to check an array that all values ​​are IS NULL?

Question

PostgreSQL

In the process of solving the issue: display NULL if all values ​​in the (result) array IS NULL (instead of an array with NULLs). The result is the following expression:

SELECT ...
CASE WHEN ('A' = ALL(ARRAY_AGG(t1.value)) IS NULL) THEN null ELSE ARRAY_AGG(t1.value)

The data type in the array is VARCHAR. But, I can't understand what it means 'A' =... If we check an array with numbers - you can write 1 =or 999 =- it works the same way ... actually, I also wrote "A" from the bulldozer and it works.
I can not understand what kind of design this is and how to understand it correctly? If you poke into the documentation, where they write about this or tell me how to correctly check the array for "all IS NULL values" or explain briefly how this expression works, they will be extremely grateful.

Answer 1

I don’t know how you did the join on your last question, but you had to first select unique records from the first table, and then join the second table to it and aggregate value into an array, then where in the second table there is nothing to the first in the array, just one null element, respectively, we take the first element of the array and look at it for null, etc.
in general something like this:

SELECT
  t.a1, t.a2, t.a3,
  CASE WHEN ( ARRAY_AGG(t2.value) )[1] IS null THEN null ELSE ARRAY_AGG(t2.value) END AS arr
FROM (
    SELECT 
      t1.a1,
      t1.a2,
      t1.a3
    FROM table1 t1
    GROUP BY t1.a1, t1.a2, t1.a3
  ) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3

but if the value of the second table is null and this element is the first, then it will work and return null for the entire array.
it would be possible to insert a check for the length of the array, but unfortunately, for unknown reasons, the ARRAY_LENGTH function does not work (i.e., it was not found) either on my pgsql 11 or in sqlfiddle , somehow strange, but they write about it in the dock ... :??
but there is ARRAY_DIMS and then the query can be rewritten like this:

SELECT
  t.a1, t.a2, t.a3,
  CASE WHEN
          (ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
      AND ( ARRAY_AGG(t2.value) )[1] IS null
    THEN null ELSE ARRAY_AGG(t2.value)
  END AS arr
FROM (
    SELECT 
      t1.a1,
      t1.a2,
      t1.a3
    FROM table1 t1
    GROUP BY t1.a1, t1.a2, t1.a3
  ) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3

those. actual answer:

CASE WHEN
          (ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
      AND ( ARRAY_AGG(t2.value) )[1] IS null
    THEN null ELSE ARRAY_AGG(t2.value)
  END