parse json_agg as json

[benbro]

Is it possible to parse the json and jsob types? Currenty pgo returns the column as binary. OTP 27 will have a json module so it might make sense.

CREATE TEMPORARY TABLE j (
    id SERIAL PRIMARY KEY,
    name varchar(10)
);

INSERT INTO j (name) VALUES ('n1');
INSERT INTO j (name) VALUES ('n2');

SELECT json_agg(json_build_array(id, name)) FROM j;

pgo:query(<<"SELECT json_agg(json_build_array(id, name)) FROM j;">>).
#{command => select,
  rows => [{<<"[[1, \"n1\"], [2, \"n2\"]]">>}],
  num_rows => 1}

I can't use array_agg instead of json_agg because arrays support only single data type.

SELECT array_agg(ARRAY[id, name]) FROM j;
ERROR:  ARRAY types integer and character varying cannot be matched

[tsloughter]

oooh, I hadn't even thought of that use case for otp-27's json yet!