postgresql 修改屬性

標籤：

up vote2down votefavorite

From this article, I tried to update or delete 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($1)     UNION ALL     SELECT * FROM JSONB_EACH($2) ) 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 is null (something like {"b":null}) the value would 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 20 ‘15 at 10:23 asked Mar 20 ‘15 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 be more efficient to keep it that way the whole time. Question 2 Just replace your function with the following (only the part WHERE key NOT IN ... added): CREATE FUNCTION jsonb_merge(JSONB, JSONB) RETURNS JSONB AS $$ WITH json_union AS (     SELECT * FROM JSONB_EACH($1)     UNION ALL     SELECT * FROM JSONB_EACH($2) ) SELECT JSON_OBJECT_AGG(key, value)::JSONB      FROM json_union      WHERE key NOT IN (SELECT key FROM json_union WHERE value =‘null‘); $$ LANGUAGE SQL; shareimprove this answer

postgresql 修改屬性