Skip to content

Instantly share code, notes, and snippets.

@mpneuried
Last active July 12, 2024 11:47
Show Gist options
  • Select an option

  • Save mpneuried/fadc340cec872e7e2c10ddf900a30f5b to your computer and use it in GitHub Desktop.

Select an option

Save mpneuried/fadc340cec872e7e2c10ddf900a30f5b to your computer and use it in GitHub Desktop.
Add and remove elements unique to a Postgres jsonb sub key: Short a Set implemetation
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 *;
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 *;
@ilaipi
Copy link
Copy Markdown

ilaipi commented Nov 2, 2021

Thanks, very useful!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment