PostgreSQL: Remove element from JSON data

Question

I am fairly new to SQL and wondering how to remove a specific element from the JSONB data below:

[
  {"id":101,"content":"lorem ipsum", "username":"user_1"},
  {"id":102,"content":"lorem ipsum", "username":"user_2"},
  {"id":103,"content":"lorem ipsum", "username":"user_3"}
]

In this case I want to remove the object with id: 102, so that the end result looks like this:

Expected Result

[
  {"id":101,"content":"lorem ipsum", "username":"user_1"},
  {"id":103,"content":"lorem ipsum", "username":"user_3"}
]

I am looking to write a PostgreSQL function that receives the id as parameter and returns the expected result above.

Any ideas on doing this with PostgreSQL?

I am working with PostgreSQL version 15.1 on Supabase.

I tried messing around with functions like jsonb_array_elements, but none of these helped me, or I understood these wrongly. Any help is appreciated, Thank you!

Answer 1

read more about PL/pgSQL:
https://www.postgresqltutorial.com/postgresql-plpgsql/
brief summary:
it creates function remove_jsonb_object, takes jsonb_data and target_id as input parameters. And returns new jsonb data, but without target_id.
Function:

CREATE OR REPLACE FUNCTION remove_jsonb_object(jsonb_data jsonb, target_id integer)
RETURNS jsonb AS
$$
DECLARE
    new_jsonb_data jsonb = '[]';
BEGIN
    FOR obj IN SELECT * FROM jsonb_array_elements(jsonb_data)
    LOOP
        IF(obj ->> 'id')::integer != target_id THEN
            new_jsonb_data = new_jsonb_data || obj;
        END IF;
    END LOOP;
    
    RETURN new_jsonb_data;
END;
$$
LANGUAGE plpgsql;

You can use this function:

SELECT remove_jsonb_object('[
    {"id":101,"content":"lorem ipsum", "username":"user_1"},
    {"id":102,"content":"lorem ipsum", "username":"user_2"},
    {"id":103,"content":"lorem ipsum", "username":"user_3"}
]'::jsonb, 102);

result output:

[
  {"id":101,"content":"lorem ipsum", "username":"user_1"},
  {"id":103,"content":"lorem ipsum", "username":"user_3"}
]