Remove Object From JSON in Postgress-CodePudding

I have a table called shopping_cart:

INSERT INTO shopping_cart( user_inv_id, dod_id, items) VALUES ( 2, 263748598, ARRAY ['{"Name":"Boot"}', '{"Name":"Hat"}']::json[] )

Im able to add to the current JSON using:

UPDATE shopping_cart SET items = items || ARRAY ['{"Name":"1"}', '{"Name":"2"}','{"Name":"3"}']::json[] WHERE dod_id = '263748598';

I cant figure out how to delete an object from the array. I would like to delte based on "Name"

CodePudding user response：

Maybe not best solution, but seems to work. We don't delete items, just create new array without one element

update shopping_cart set
items = 
(select array_agg(elem) from  unnest(items) AS a(elem)  
    where elem->>'Name' <> 'Hat'
)
where dod_id = 263748598

CodePudding user response：

Like luck would have it, after I posted I refactored the table to JSONb and created a new call and it worked...

//Table Creation

CREATE TABLE shopping_cart ( dod_id varChar PRIMARY KEY, user_name varchar(250), items jsonb );

//Add to JSON cell within items column

UPDATE shopping_cart SET items = items || '{"Name": "Sock", "Size": "Medium", "uuid": “2”}’ ::jsonb WHERE dod_id = '263748598';

//Delete index within JSONb cell

UPDATE shopping_cart SET items = items - Cast((SELECT position - 1 FROM shopping_cart, jsonb_array_elements(items) with ordinality arr(item_object, position) WHERE dod_id='263748598' and item_object->>'uuid' = '2') as int) WHERE dod_id='263748598';