Last active
December 16, 2022 19:11
-
-
Save pgebert/8aaebb3ff73a8b0987e4ab0830ee6a89 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION jsonb_replace_by_key(obj jsonb, search text, substitute jsonb) RETURNS jsonb | |
STRICT LANGUAGE SQL AS $$ | |
SELECT CASE jsonb_typeof(obj) | |
WHEN 'object' THEN | |
(SELECT coalesce(jsonb_object_agg(key, CASE WHEN key = search | |
THEN substitute | |
ELSE jsonb_replace_by_key(value, search, substitute) | |
END), '{}'::jsonb) | |
FROM jsonb_each(obj)) | |
WHEN 'array' THEN | |
(SELECT coalesce(jsonb_agg(jsonb_replace_by_key(el, search, substitute)), '[]'::jsonb) | |
FROM jsonb_array_elements(obj) el) | |
ELSE | |
obj | |
END; | |
$$; | |
-- Exammple usage | |
create table test | |
( | |
id int primary key, | |
js jsonb | |
); | |
insert into test values (1, '{"id": "total", "price": null, "breakdown": [{"id": "product1", "price": 400}] }'); | |
insert into test values (2, '{"id": "total", "price": 1000, "breakdown": [{"id": "product1", "price": 400}, {"id": "product2", "price": 600}]}'); | |
update test set js = jsonb_replace_by_key(js, 'price', 'null'::jsonb); | |
select * from test; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment