1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
ittos2
  • 11
  • 2
  • Please only tag with relevant tags for the problem. Since there is no JavaScript code nor PHP code, I removed these two tags. – VLAZ Aug 25 '20 at 09:47
  • 1
    Are you trying to create a JSON array value? Why the `[` characters? As you return an array you don't need additional characters to delimit the elements of the array –  Aug 25 '20 at 09:48
  • @a_horse_with_no_name yes sir, but somewhere like array_to_string function with param separator on it ? – ittos2 Aug 25 '20 at 09:50
  • @Steven That cannot be correct. The suggest edit privilege is open to everyone, you don't even need an account - anonymous users can suggest edits. I'm confident that question authors can always edit their own questions, too - similar to how they can comment. Users have more privileges on their own posts. – VLAZ Aug 25 '20 at 09:58

1 Answers1

2

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
  • Thanks for advance.I forgot try with jsonB_agg i lost for 1 hour with json_agg.First error disapper and i'm very excited with some case used to be by you @a_horse_with_no_name – ittos2 Aug 25 '20 at 10:05
  • I have a final nice day.Thank you very much bro!!! i used first solution.Nice with many items in one json array. – ittos2 Aug 25 '20 at 10:18