title: postgres array_agg ERROR: cannot accumulate arrays of different dimensionality
body: <p>I have a parcels table in postgresql in which the zoning and zoning_description columns are array_agg cast over to text. the new.universities table has 9 rows and I need to return 9 rows in the output. </p>
  
  <p>The purpose of this query is to find all the properties these universities are located on and collapse there zoning types into 1 unique column and union/dissolve their geometries into multipolygons</p>
  
  <pre><code>select array_agg(distinct dp.zoning) zoning,array_agg(distinct dp.zoning_description) zoning_description,
      uni.school name_,uni.address,'University' type_,1000 buff,st_union(dp.geom) 
  from new.universities uni join new.detroit_parcels_update dp
  on st_intersects(st_buffer(uni.geom,-10),dp.geom)
  group by name_,uni.address,type_,buff
  </code></pre>
  
  <p>I get this error</p>
  
  <pre><code>ERROR:  cannot accumulate arrays of different dimensionality
  ********** Error **********
  
  ERROR: cannot accumulate arrays of different dimensionality
  SQL state: 2202E
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/uhzMW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/uhzMW.png" alt="enter image description here"></a></p>
  
  <p>I can do array_agg(distinct dp.zoning::text) zoning etc.. but this returns a completely messed up column with nested arrays in arrays...
  <a href="https://i.stack.imgur.com/PQXDz.png" rel="noreferrer"><img src="https://i.stack.imgur.com/PQXDz.png" alt="enter image description here"></a></p>
  
  <p>Based on the answer here is my updated query which does not work</p>
  
  <pre><code>select array_agg(distinct zoning_u) zoning,array_agg(distinct zoning_description_u) zoning_description,
          uni.school name_,uni.address,'University' type_,1000::int buff,st_union(dp.geom) geom
  from new.detroit_parcels_update dp,unnest(zoning) zoning_u,
  unnest(zoning_description) zoning_description_u
  join new.universities uni
  on st_intersects(st_buffer(uni.geom,-10),dp.geom)
  group by name_,uni.address,type_,buff order by name_
  </code></pre>
  
  <p>get this error </p>
  
  <pre><code>ERROR:  invalid reference to FROM-clause entry for table "dp"
  LINE 6: on st_intersects(st_buffer(uni.geom,-10),dp.geom)
                                                   ^
  HINT:  There is an entry for table "dp", but it cannot be referenced from this part of the query.
  
  ********** Error **********
  
  ERROR: invalid reference to FROM-clause entry for table "dp"
  SQL state: 42P01
  Hint: There is an entry for table "dp", but it cannot be referenced from this part of the query.
  Character: 373
  </code></pre>
  
  <p>My Final query which worked was</p>
  
  <pre><code>with t as(select dp.zoning,dp.zoning_description,uni.school name_,uni.address,'University' type_,1000::int buff,st_union(dp.geom) geom
      from new.detroit_parcels_update dp
      join new.universities uni
      on st_intersects(st_buffer(uni.geom,-10),dp.geom)
      group by name_,uni.address,type_,buff,dp.zoning,zoning_description order by name_
      )
  select name_,address,type_,buff,st_union(geom) geom,array_agg(distinct z) zoning, array_agg(distinct zd) zoning_description
  from t,unnest(zoning) z,unnest(zoning_description) zd 
  group by name_,address,type_,buff
  </code></pre>