How to select an array with multiple columns in postgres?-CodePudding

This is for postgres 12.8. Let's say I have a customer table and an address table, which are joined by a customer_id key.

I'm trying to do do one query to select all customers and an array of their address (street, city, state, zip code)

to end up with an array for a struct that looks like this:

type Customer struct{
   customer_id string
   address     []Address
}

type Address struct {
   street string
   city   string
   state  string
   zip   string
}

Doing a join doesnt work because each row has a customer id.

I tried to do something like


select customer_id, (select array_agg(street, city, state, zip) from address where customer_id= c.id) from customer c

But i get an error that a fucntion doesnt exist.

I feel selecting another table as an array is pretty common, but I've never ran into it before. I also couldnt find a good example for this online anywhere.

Thanks for your help.

CodePudding user response：

Something like this?

SELECT
  customer_id,
  array_agg(row(street, city, state, zip))
FROM
  customer_address
GROUP BY
  customer_id

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc0d405710375207f412d81dee96dd70