How to get JavaScript/JSON array of array in Postgresql?

Question

JSON object format is verbose:

"[{"id":1,"name":"John"}, {"id":2,"name":"Jack"}]"

Sometimes, repeating field names take more space than the actual data. To save bandwidth, and speed up page loading, I would like to generate a JavaScript array of arrays in string format instead and send it to the client. For example for this data:

create table temp (
  id int,
  name text
);

insert into temp values (1, 'John'), (2, 'Jack');

I would like to get '[[1, "John"], [2, "Jack"]]'. How can I do that?

I do not want to aggregate columns by typing them out, since that would be hard to maintain. I also know postgresql does not allow multiple types in an array like JavaScript, so one possibility is to use composite types, but then, stringified/aggregated result ends up having '()' in them.

Answer 1

select array_to_json(array_agg(json_build_array(id, name)))
from temp;
       array_to_json       
---------------------------
 [[1, "John"],[2, "Jack"]]