PostgreSQL’s JSONB data type is incredibly powerful for storing semi-structured data, and a common use case is managing arrays within your columns. This guide will walk you through essential SQL operations for adding new rows with JSONB arrays, and then updating existing arrays by adding or removing values.

Adding New Rows with JSONB Arrays
When you need to insert a new record where one of the columns stores an array of values, you can directly provide a JSON array literal. This example demonstrates adding a new user with an array of departments:

INSERT INTO USER (DEPARTMENTS)
VALUES ('["dept1","dept2"]');

Adding a New Value to an Existing JSONB Array
To append a new element to an existing JSONB array in a column, you can use the UPDATE statement along with the || operator, which acts as a concatenation operator for JSONB values. This allows you to easily add new departments to a user’s record:

UPDATE USER
SET DEPARTMENTS = DEPARTMENTS || '["dept3"]'::JSONB;

Removing a Value from a JSONB Array
If you need to remove a specific value from a JSONB array within a column, you can use the UPDATE statement with the - operator. This operator allows you to subtract a specified JSONB element from the array. Here’s how to remove a department:

UPDATE USER
SET DEPARTMENTS = DEPARTMENTS - '["dept1"]'::JSONB;

By mastering these fundamental SQL operations, you can effectively manage dynamic array data within your PostgreSQL JSONB columns, offering great flexibility for your application’s data structures.