CODEWITHSUNDEEP
sqlpostgresqlarray-agg
ittos2
ittos2

Reputation: 11

Array_agg function

I have multiple column to aggregate in one new column .I wanna Concatenate them in one array and separator with ',' between them.Can you tell me some advances to write query .My example i want work simple and faster :

  SELECT array_agg('[' || us.name || ',' || us.age || ',' || us.gender || ']') 
  FROM users as us;

Upvotes: 1

Views: 837

Answers (1)

user330315
user330315

Reputation:

If you want to create a JSON array, then use JSON functions:

select jsonb_agg(concat_ws(',', us.name, us.age, us.gender))
from users as us;

If you want real JSON objects (using key/value pairs) rather than comma separated strings inside the array:

select jsonb_agg(to_jsonb(u))
from (
  select name, age, gender
  from users
) u

Another option is to created nested arrays:

select jsonb_agg(to_jsonb(array[name, age, gender]))
from users

Upvotes: 2

Related Questions