How to remove NULL values in multidimensional array?

Question

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
      ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as arrayofdatatypevalues
FROM datatypevalue dtv
FULL JOIN public.datatype as dt on dt.datatypeid = dtv.datatypeid
WHERE dt.name !='Integer' and dt.name !='String' and dt.name !='Boolean'
GROUP BY dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid;   

Returns output like (example with two result rows):

{{NULL,NULL,NULL,NULL,NULL}}
{{NULL,10,xxs,1,21},{NULL,9,xyz,0,21}}

How do I replace this {{NULL,NULL,NULL,NULL,NULL}} with {}?

Answer 1

You can do it the slow and verbose way:

SELECT name, description, odobj, defval, datatypeid
     , CASE WHEN arrayofdatatypevalues = '{{NULL,NULL,NULL,NULL,NULL}}'::text[]
            THEN '{}' ELSE arrayofdatatypevalues END AS arrayofdatatypevalues
FROM  (
   -- your org. query here
   ) sub;

My educated guess, though: you don't actually want FULL [OUTER] JOIN to begin with. Looks like a LEFT [OUTER] JOIN to me. (Basics in the manual here.)

...
FROM   public.datatype dt
LEFT   JOIN datatypevalue dtv USING (datatypeid)
...

If so, transform it into into a LATERAL subquery or just a correlated subquery:

SELECT dt.name, dt.description, dt.odobj, dt.defval, dt.datatypeid
     , ARRAY(
          SELECT ARRAY[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]
          FROM   public.datatypevalue dtv
          WHERE  dtv.datatypeid = dt.datatypeid
          ) AS arrayofdatatypevalues
FROM   public.datatype dt
WHERE  dt.name <> ALL ('{Integer,String,Boolean}'::text[]);

Returns {} instead of {{NULL,NULL,NULL,NULL,NULL}} out of the box if no matching row is found in table datatypevalue. But still returns {{NULL,NULL,NULL,NULL,NULL}} for a single rows with all retrieved columns holding an actual NULL value, which can be a very important distinction to make. (Probably not possible with your setup - another guess in want of actual table definitions.)

The need for GROUP BY goes away.
While being at it, use a faster ARRAY constructor instead of array_agg(). See:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

db<>fiddle here for Postgres 12.