PostgreSQL Tutorial: Modify nested arrays in JSON columns

Summary: In this article, we will learn how to modify nested arrays in PostgreSQL JSON columns.

Table of Contents

Postgres does not provide modification operations for nested JSON arrays out-of-the-box. By combining some JSON utility functions with array functions you can achieve dynamic update statements to do adding/removing by value.

Example

Let’s create a simple user table with an id, username and metadata attribute which is of type jsonb:

CREATE TABLE users (
    id serial PRIMARY KEY,
    username TEXT NOT NULL,
    meta_data JSONB NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO users (username, meta_data)
VALUES ('johndoe', '{"roles": ["hr", "marketing", "sales"]}'::jsonb);

SELECT id, username, meta_data FROM users;

Which will print:

 id | username |                meta_data
----+----------+-----------------------------------------
  1 | johndoe  | {"roles": ["hr", "marketing", "sales"]}
(1 row)

Adding elements

Let’s add the “development” and “administration” roles to the users roles array:

UPDATE users
SET meta_data = jsonb_set(meta_data, ARRAY['roles'],
    COALESCE(meta_data->'roles', '[]'::jsonb) ||
    array_to_json(ARRAY['development', 'administration'])::jsonb)
WHERE id = 1;

Adding is rather simple. The function jsonb_set takes as first parameter the json structure you want to update, as second parameter a PostgreSQL array of identifiers/indices leading to the part to update and as third parameter the new value. You could even address the first role of the user and replace it by a new value:

UPDATE users
SET meta_data = jsonb_set(meta_data, '{roles,0}', '"administration"'::jsonb)
WHERE id = 1;

Removing element

Removing one role is a bit more complicate:

UPDATE users
SET meta_data = jsonb_set(meta_data, ARRAY['roles'],
    array_to_json(
        array(
            SELECT arr.elem
                FROM jsonb_array_elements(meta_data->'roles')
                     WITH ordinality arr(elem, position)
                WHERE arr.elem != '"administration"'::jsonb)
        )::jsonb)
WHERE id = 1;

We can use the jsonb_array_elements function to convert a JSON array into a set of rows. The row set can then be filtered with a normal WHERE condition. After filtering, the result set is passed to the array() constructor, which converts it back to the array. After that, we can apply the array_to_json function to the new array, converting it back to the JSON array.