Question 232049 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL to get json output in Postgres

HI Team am trying to generate output from a table but tried unable to get it.

create table t1(a int, b int, c text);
insert into t1 values (1, 1, 'm');
insert into t1 values (1, 2, 'm');
insert into t1 values (2, 7, 'm');
insert into t1 values (2, 8, 'n');
insert into t1 values (2, 9, 'o');

select array_to_json(array_arg(row_to_json(t)))
from (select a,b from t1)

Got this

[{'a':1, 'b':2'}, {'a':1, 'b':2}, {'a':2,'b':7}, {'a':2, 'b':8}, {'a':2, 'b':9}]

Desired output

`[{'a':1, 'b':[1,2]}, {'a':2,'b': [7,8,9]}]`
Tags:

2

Answers


  1. 0

    You were on the right lines! The following will do most of what you want:

    WITH cte as (
    SELECT a, array_agg(b) as b
    FROM t1
    GROUP BY a
    ORDER BY a
    )
SELECT json_agg(cte) 
FROM cte;

    Whenever possible, use json_agg – it usually does what you want!

    If you really want single quotes, then simply add a replace:

    SELECT REPLACE(json_agg(cte)::text, '"', '''')

    (Note that you have to escape the single quotes, as single quotes in SQL delineate a string.)

    Login or Signup to reply.
  2. 0

    I use the jsonb type instead of json.

    Rather than deal with text[] array types, I would do the following:

    with first_agg as (
  select a, jsonb_agg(b) as b
    from t1
   group by a
)
select to_jsonb(first_agg)
  from first_agg;

    Working fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search