How to Remove jsonb Object from Nested jsonb Array in Postgres

JSONB is a powerful and flexible data type in PostgreSQL that allows you to store complex, hierarchical data in a single column. However, working with JSONB data can sometimes be tricky, especially when it comes to removing specific objects from nested arrays.

In this article, we will show you how to remove a JSONB object from a nested JSONB array in PostgreSQL. We will assume that you have a basic understanding of JSONB and PostgreSQL, but we will try to explain each step in detail to make it easy to follow along.

Prerequisites

Before we begin, let's make sure you have everything you need to follow along:

• A PostgreSQL database
• A table with a JSONB column containing nested arrays
• A sample JSONB object to remove from the nested array

For the purposes of this article, we will use the following sample data:

{
"name": "John Doe",
"skills": [
{"name": "Java", "level": "expert"},
{"name": "Python", "level": "intermediate"},
{"name": "SQL", "level": "beginner"}
]
}

Suppose we want to remove the object with the name "Python" from the "skills" array.

Removing a JSONB Object from a Nested Array

To remove a JSONB object from a nested array, we can use the following steps:

1. Use the jsonb_array_elements function to extract the nested array as a set of rows
2. Use a WHERE clause to filter the rows based on the JSONB object to remove
3. Use the jsonb_agg function to re-aggregate the filtered rows into a JSONB array
4. Update the original JSONB column with the new array

Let's go through each step in detail.

Step 1: Extract the Nested Array

To extract the nested array as a set of rows, we can use the jsonb_array_elements function. This function takes a JSONB array as an argument and returns a table with one row for each element in the array. Each row contains the element's JSONB value as a column named value.

SELECT jsonb_array_elements(data->'skills') as skill
FROM my_table;

In this example, we are extracting the skills array from the data column in the my_table table. The result is a table with one row for each skill object in the array.

Step 2: Filter the Rows

To filter the rows based on the JSONB object to remove, we can use a WHERE clause. For example, to remove the object with the name "Python", we can use the following query:

SELECT jsonb_array_elements(data->'skills') as skill
FROM my_table
WHERE (skill->>'name') = 'Python';

In this example, we are filtering the rows based on the name property of the skill object. We are using the ->> operator to extract the name property as a text value and comparing it to the string "Python" using the = operator.

Step 3: Re-aggregate the Filtered Rows

To re-aggregate the filtered rows into a JSONB array, we can use the jsonb_agg function. This function takes a set of JSONB values and returns a JSONB array containing those values.

SELECT jsonb_agg(skill) as skills
FROM (
SELECT jsonb_array_elements(data->'skills') as skill
FROM my_table
WHERE (skill->>'name') != 'Python'
) as filtered;

In this example, we are using a subquery to filter the rows based on the name property of the skill object. We are using the jsonb_array_elements function to extract the nested array as a set of rows, and the WHERE clause to filter the rows based on the JSONB object to remove.

We are then using the jsonb_agg function to re-aggregate the filtered rows into a JSONB array, which we are renaming to skills using the AS clause.

Step 4: Update the Original JSONB Column

Finally, to update the original JSONB column with the new array, we can use the SET clause of the UPDATE statement. We can use the = operator to assign the new array to the column.

UPDATE my_table
SET data = jsonb_set(data, '{skills}', filtered.skills)
FROM (
SELECT jsonb_agg(skill) as skills
FROM (
SELECT jsonb_array_elements(data->'skills') as skill
FROM my_table
WHERE (skill->>'name') != 'Python'
) as filtered
) as filtered
WHERE id = 1;

In this example, we are using the jsonb_set function to replace the skills array in the data column with the new array. We are using the {} syntax to specify the path to the skills array in the data column.

We are then using a subquery to filter the rows based on the name property of the skill object, re-aggregate the filtered rows into a JSONB array, and rename the array to skills.

We are finally using the UPDATE statement to update the data column in the my_table table with the new array. We are using the WHERE clause to filter the rows based on the id column.

Removing a JSONB object from a nested array in PostgreSQL can be a bit tricky, but it's definitely possible. By following the steps outlined in this article, you can remove specific JSONB objects from nested arrays and update the original JSONB column with the new array.

References