Skip to content

Instantly share code, notes, and snippets.

@monaxmp

monaxmp/postgres_add_json_sub_array_unique.sql

Forked from mpneuried/postgres_add_json_sub_array_unique.sql
Created July 27, 2019 12:43
Show Gist options
  • Save monaxmp/26b76ecc086fdd21da975b95bc8fbcfa to your computer and use it in GitHub Desktop.
Save monaxmp/26b76ecc086fdd21da975b95bc8fbcfa to your computer and use it in GitHub Desktop.
Download ZIP
Add and remove elements unique to a Postgres jsonb sub key: Short a Set implemetation
Raw
postgres_add_json_sub_array_unique.sql
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 *;
Raw
postgres_remove_json_sub_array.sql
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 *;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment