3

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:

  1. is there any drawback of using JSONB_EACH (jsonb_merge) instead of JSONB_EACH_TEXT (from the article) in this case?

  2. 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}
3

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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.