PostgreSQL: How to Concatenate JSON Arrays

Often in PostgreSQL you may want to concatenate multiple JSON arrays together.

You can use the following basic syntax to do so:

SELECT
    team,
    jsonb_agg(elems) AS roles
FROM
    athletes,
    jsonb_array_elements(player_roles) AS elems
GROUP BY team;

This particular example will concatenate the JSON arrays in the player_roles column, grouped by the team column of the athletes table.

Note that we use the jsonb_array_elements() function to expand each element from the JSON arrays into their own row.

We then use the jsonb_agg() function to aggregate all of the values from the JSON arrays into a single array, grouped by the team column.

The following example shows how to use this syntax in practice.

Example: How to Concatenate JSON Arrays in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  game_id INTEGER,
  team TEXT,
  player_roles JSONB
);

-- add rows to the table 
INSERT INTO athletes VALUES (1, 'Mavs', '["Guard", "Center"]');
INSERT INTO athletes VALUES (2, 'Mavs', '["Guard", "Forward"]');
INSERT INTO athletes VALUES (3, 'Kings', '["Guard", "Forward"]');
INSERT INTO athletes VALUES (4, 'Kings', '["Center", "Center"]');
INSERT INTO athletes VALUES (5, 'Lakers', '["Guard", "Center"]');
INSERT INTO athletes VALUES (6, 'Lakers', '["Forward", "Forward"]');

-- view all rows in table
SELECT * FROM athletes;

 game_id |  team  |      player_roles      
---------+--------+------------------------
       1 | Mavs   | ["Guard", "Center"]
       2 | Mavs   | ["Guard", "Forward"]
       3 | Kings  | ["Guard", "Forward"]
       4 | Kings  | ["Center", "Center"]
       5 | Lakers | ["Guard", "Center"]
       6 | Lakers | ["Forward", "Forward"]

Suppose that we would like to concatenate the values from the JSON arrays in the player_roles column, grouped by the values in the team column.

We can use the following syntax to do so:

SELECT
    team,
    jsonb_agg(elems) AS roles
FROM
    athletes,
    jsonb_array_elements(player_roles) AS elems
GROUP BY team;

  team  |                   roles                   
--------+-------------------------------------------
 Lakers | ["Guard", "Center", "Forward", "Forward"]
 Kings  | ["Guard", "Forward", "Center", "Center"]
 Mavs   | ["Guard", "Center", "Guard", "Forward"]

Notice that the values from the JSON arrays in the player_roles column have been concatenated into a single array, grouped by the values in the team column.

Feel free to use similar syntax to concatenate JSON arrays in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Filter Table by JSONB Field
PostgreSQL: How to Remove Element from JSONB Array
PostgreSQL: How to Get Last Item in JSON Array