How to make some jsonb field's value to a json array in PostgreSQL?

Question

Here is a table:

create table test(
  fjson jsonb
);

then insert some value:

insert into test values
('{"name":"A"}'::jsonb),
('{"name":"B"}'::jsonb),
('{"name":"C"}'::jsonb);

I want to get a json array like below:

[
  {"name":"A"},
  {"name":"B"},
  {"name":"C"}
]

How to write the SQL?

Thanks.

Problem solved. thanks

do $$ declare
    jdata jsonb;
begin
    create temp table test(
      fjson jsonb
    ) on commit drop;
    
    insert into test values
    ('{"name":"A"}'::jsonb),
    ('{"name":"B"}'::jsonb),
    ('{"name":"C"}'::jsonb);
    
    select (select jsonb_agg(fjson) from test) into jdata;
    raise notice '%',jdata;
end;
$$ language plpgsql;

Bergi · Accepted Answer · 2021-11-21 14:35:58Z

1

You can use

SELECT jsonb_agg(fjson) from test

to get a jsonb array, or

SELECT array_agg(fjson) from test

to get a native pg array.

edited Nov 21, 2021 at 14:35

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

answered Nov 21, 2021 at 14:08

Marcel Cremer

1166 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make some jsonb field's value to a json array in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related