Postgres: jsonb to jsonb[]?

Question

Let's say I have this table:

ams=# \d player
                               Table "public.player"
   Column    |           Type           | Collation | Nullable |      Default
-------------+--------------------------+-----------+----------+-------------------
 id          | integer                  |           | not null |
 created     | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 player_info | jsonb                    |           | not null |

And then I have this:

ams=# \d report
                        Table "public.report"
 Column  |           Type           | Collation | Nullable | Default
---------+--------------------------+-----------+----------+---------
 id      | integer                  |           | not null |
 created | timestamp with time zone |           | not null |
 data    | jsonb[]                  |           | not null |

How can I take the player_info from all the rows in the player table and insert that into a single row in the report table (into the data jsonb[] field)? My attempts with jsonb_agg() return a jsonb, and I can't for the life of me figure out how to go from jsonb to jsonb[]. Any pointers would be very much appreciated! Thanks in advance.

In my opinion jsonb[] almost never makes sense. Use jsonb as well and create a json array instead of an array of json. — user330315
– user330315, Commented Aug 14, 2020 at 17:16

Ruben Helsloot · Accepted Answer · 2020-08-14 16:03:39Z

2

If you plainly want to copy the values, just treat it like any other data type, and use ARRAY_AGG.

SELECT ARRAY_AGG(player_info)
FROM player
WHERE id IN (...)

should return something of type json[].

answered Aug 14, 2020 at 16:03

Ruben Helsloot

13.2k6 gold badges33 silver badges56 bronze badges

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

Comments

Mike Organek · Accepted Answer · 2020-08-14 16:03:49Z

2

Since jsonb[] is an array at the type level in PostgreSQL vs. a json array, use array_agg() instead of jsonb_agg().

insert into report 
select 1 as id, now() as created, array_agg(player_info)
  from player
;

answered Aug 14, 2020 at 16:03

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

1 Comment

stianlagstad Over a year ago

Thank you. Ruben beat your answer by 10ms so I had to chose his answer :)

Collectives™ on Stack Overflow

Postgres: jsonb to jsonb[]?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related