修改jsonb的属性

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;

 

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}

 

 

 

 

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;