Based on Northwind schema:

Assume we have a query like this (please disregard the actual usefulness of this, as the real use case is more involved)

SELECT unnest(r.agg) FROM (   SELECT array_agg(x) AS agg   FROM (      SELECT *      FROM northwind.u_suppliers s LEFT JOIN northwind.u_products p ON s.guid = p.supplier      ) x   GROUP BY x.supplier   ) r LIMIT 1; 

the result is

(9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,22,"Dirk Luchte","Accounting Manager",6j7qZ3Pjad5irKAM1MKX1WU7hvkgIOhZoe9OeRfTX4Q,"(12345) 1210",,northwindSupplier,jp9VvzFBbklHelD_ybS1oDsJbILda1duNhMtCdGBZqg,0,do4_k8zCsOog5tYROlsouF81Uj9I5LbTFawvuPw9Q-0,"2017-10-11 08:45:14.982",47,"Zaanse koeken",9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,AkOUoKYOpHFWIkNcoPX3CLtTl4n_ACN6TwWxqd3gzYQ,"10 - 4 oz boxes",9.5,36,0,0,f,,northwindProduct) 

which is a record.

What would be the way to create a table from this with all columns and unnested records as rows?

So there’d be:

u_suppliers_column1 | ... | u_suppliers.columnN | u_products.column1 | ... | u_products.columnN ------------------------------------------------------------------------------------------------ 9Y_03og...          | 22 | "Dirk Luchte" ... 

The snippet is for illustrational purpose only – of course, creating aggregate just to unnest it again is non-sensible under these circumstances, but for sake of simplicity & reproducibility I have decided to write it as such,.