PostgreSQL Modify Properties

Up Vote2down Votefavorite

From the article, I tried to update or delete the property of a jsonb column: CREATE TABLE xxx (id bigserial, data jsonb); INSERT into XXX (data) VALUES (' {' A ': 1, ' B ': 2} '); SELECT * from data; ID | Data ----+------------------ 1 | {"A": 1, "B": 2} Create the update function: CREATE FUNCTION Jsonb_merge (jsonb, JSONB) RETURNS Jsonb as $$ With Json_union as ( SELECT * from Jsonb_each ($) UNION All SELECT * from Jsonb_each ($) ) SELECT Json_object_agg (key, value):: Jsonb from Json_union; $$ LANGUAGE SQL; Testing: --Replace UPDATE xxx SET data = jsonb_merge (data, ' {"B": 3} ') WHERE id = 1; SELECT * from XXX; ID | Data ----+------------------ 1 | {"A": 1, "B": 3} --Append UPDATE xxx SET data = jsonb_merge (data, ' {"C": 4} ') WHERE id = 1; SELECT * from XXX; ID | Data ----+------------------------- 1 | {"A": 1, "B": 3, "C": 4} The question is: Is there any drawback of using Jsonb_each (Jsonb_merge) instead of Jsonb_each_text (from the article) in this case? How to modify the Jsonb_merge so if the second parameter property value was null (something like {"B": null}) The value Woul D be erased? . --Remove UPDATE xxx SET data = jsonb_merge (data, ' {"B": null} ') WHERE id = 1; SELECT * from XXX; ID | Data ----+----------------- 1 | {"A": 1, "C": 4} PostgreSQL postgresql-9.4 Shareimprove this question Edited Mar at 10:23 Asked Mar at 6:06 Kokizzu 273312

Add a Comment

1 Answer

Activeoldestvotes

Up Vote3down voteaccepted

Question 1 There should be no signicant drawbacks. As the value is converted back to jsonb anyhow I would guess it would being more efficient to keep it's that's the whole time . Question 2 Just Replace your function with the following (only the "WHERE key not in ... added): CREATE FUNCTION Jsonb_merge (jsonb, JSONB) RETURNS Jsonb as $$ With Json_union as ( SELECT * from Jsonb_each ($) UNION All SELECT * from Jsonb_each ($) ) SELECT Json_object_agg (key, value):: jsonb From Json_union Where key not in (the SELECT key from json_union WHERE value = ' null '); $$ LANGUAGE SQL; Shareimprove this Answer

PostgreSQL Modify Properties