CODEWITHSUNDEEP
postgresql
stianlagstad
stianlagstad

Reputation: 3302

Postgres: jsonb to jsonb[]?

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.

Upvotes: 0

Views: 154

Answers (2)

Mike Organek
Mike Organek

Reputation: 12494

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
;

Upvotes: 2

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

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[].

Upvotes: 2

Related Questions