Question 198211 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Remove element from JSON data

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!

Tags:

3

Answers


  1. 0

    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"}
]
    Login or Signup to reply.
  2. 0

    The following idea might likely not be the shortest and not the simplest possible, but is very clear and easy to understand.

    See here the working example according to your data: db<>fiddle

    So we use JSONB_ARRAY_ELEMENTS within a CTE to split the data in rows. Then we filter out the part with id = 102 because it should be removed.

    Then we use JSON_AGG to put the remaining parts together again.

    So, this will do:

    WITH previousResult AS
(SELECT value AS part 
  FROM JSONB_ARRAY_ELEMENTS((SELECT yourcolumn FROM yourtable))),
newResult AS 
(SELECT * FROM previousResult 
  WHERE part -> 'id' <> '102')
UPDATE yourtable SET yourcolumn = (SELECT JSON_AGG(part) FROM newResult);

    As mentioned above, the linked fiddle show this, we can check every step there.
    Of course, you can put this in any function you like, the 102 can be set as parameter.

    This will be the function then:

    CREATE OR REPLACE FUNCTION REMOVE_ID(id_to_remove int)
  RETURNS JSONB AS $$
BEGIN
  WITH previousResult AS
(SELECT value AS part 
  FROM JSONB_ARRAY_ELEMENTS((SELECT yourcolumn FROM yourtable))),
newResult AS 
(SELECT * FROM previousResult 
  WHERE CAST(part -> 'id' AS INT) <> id_to_remove)
UPDATE yourtable SET yourcolumn = (SELECT JSON_AGG(part) FROM newResult);
  RETURN (SELECT yourcolumn FROM yourtable);
END;
$$ LANGUAGE plpgsql;

    I added another fiddle that shows the function is also working correctly.

    See here: db<>fiddle

    Please note this answer assumes you already stored the jsonb data which should be modified as a column in a table.
    If this is not the case, I wouldn’t use a SQL function for such purposes, rather do that logic in your application. This should even be considered if you do store it in a column. Generally spoken, this kind of tasks is often be better done without SQL.

    Login or Signup to reply.
  3. 0

    To remove a specific element from a JSONB array in PostgreSQL, you can use the jsonb_array_elements() function along with the jsonb_agg() function. Here’s an example of a PostgreSQL function that receives an id parameter and returns the JSONB array with the specified object removed:

    CREATE OR REPLACE FUNCTION remove_jsonb_element(id_to_remove INTEGER)
  RETURNS JSONB AS $$
DECLARE
  result JSONB;
BEGIN
  SELECT jsonb_agg(elem)
  INTO result
  FROM (
    SELECT *
    FROM jsonb_array_elements('[{"id":101,"content":"lorem ipsum","username":"user_1"},{"id":102,"content":"lorem ipsum","username":"user_2"},{"id":103,"content":"lorem ipsum","username":"user_3"}]') AS elem
    WHERE (elem->>'id')::INTEGER <> id_to_remove
  ) subquery;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql;

    This function takes an id_to_remove parameter, which specifies the id of the object to be removed. It uses the jsonb_array_elements() function to expand the JSONB array into individual elements. Then, it filters out the element with a matching id_to_remove value. Finally, it uses jsonb_agg() to aggregate the remaining elements back into a JSONB array and returns the result.

    You can call this function like this:

    SELECT remove_jsonb_element(102);

    This will return the expected result:

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

    Please note that you may need to adjust the JSONB array input in the function according to your actual data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search