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: -- replaceUPDATE xxx SET data = jsonb_merge(data,‘{"b":3}‘) WHERE id = 1;SELECT * FROM xxx; id | data ----+------------------ 1 | {"a": 1, "b": 3}-- appendUPDATE 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 (from the article) in this case JSONB_EACH_TEXT ? How to modify the and jsonb_merge if the second parameter property value are null (something like {"b":null} ) the value would be erase D?
. -- removeUPDATE 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
| Share|improve this question |
Edited Mar ' at 10:23 |
Asked Mar ' at 6:06 kokizzu2733 |
|
