Skip to content

Instantly share code, notes, and snippets.

View greggy's full-sized avatar

Grigory Fateyev (aka greg) greggy

12 followers · 32 following
View GitHub Profile
@greggy
greggy / postgres_add_json_sub_array_unique.sql
Created January 25, 2020 06:35 — forked from mpneuried/postgres_add_json_sub_array_unique.sql
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 *;
@greggy
greggy / postgres_add_json_sub_array_unique.sql
Created January 25, 2020 06:35 — forked from mpneuried/postgres_add_json_sub_array_unique.sql
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 *;