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 {}?

• Laurenz Albe: Strange. If you just omit NULLs, how do you know which array member is what?
• Erwin Brandstetter: Please remember to lead with Postgres version and table definitions.

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.