mpneuried
8/23/2017 - 6:08 AM

Add and remove elements unique to a Postgres jsonb sub key: Short a Set implemetation

Add and remove elements unique to a Postgres jsonb sub key: Short a Set implemetation

postgres_remove_json_sub_array.sql
content_copyfile_download
UPDATE public.mytable SET
jsonfieldname = jsonb_set( jsonfieldname, '{json_obj_key}', array_to_json(
	array_remove( ARRAY(
		SELECT json_array_elements_text( COALESCE( jsonfieldname::json->'json_obj_key', '[]' ) )
	), 'Element to remove' )
)::jsonb )
WHERE id = 23
RETURNING *;
postgres_add_json_sub_array_unique.sql
content_copyfile_download
UPDATE public.mytable SET
jsonfieldname = jsonb_set( jsonfieldname, '{json_obj_key}', array_to_json(
	ARRAY(
		SELECT DISTINCT( UNNEST( ARRAY(
			SELECT json_array_elements_text( COALESCE( jsonfieldname::json->'json_obj_key', '[]' ) )
		) || ARRAY['Element to add'] ) )
	)
)::jsonb )
WHERE id = 23
RETURNING *;