CODEWITHSUNDEEP
postgresqlplpgsql
IndiaSke
IndiaSke

Reputation: 358

plpgsql function adding key-value to JSONB

What plpgsql function would you write to add a key value element to a JSONB column ?

+--------+---------------+-------------------+
| id[PK] | name(varchar) |   value(JSONB)    |
+--------+---------------+-------------------+
|      1 | test          | {"key1":"value1"} |
+--------+---------------+-------------------+

I use this SQL query to add a new element to the JSONB :

UPDATE output_controlling_variables SET value = value || '{"key1" : "value1"}' WHERE id = 1

What would be a plpgsql function to do this by taking key(str) and value(str) as arguments ? I am trying with this :

CREATE OR REPLACE FUNCTION add_id(test_id varchar,test_name varchar)
BEGIN
    --RETURN QUERY EXECUTE
    QUERY EXECUTE
    format('UPDATE output_controlling_variables SET value = value || %s WHERE id = 4')
END;
$$ LANGUAGE plpgsql;

Should I format the '{"key1" : "value1"}' part ?

Upvotes: 1

Views: 190

Answers (1)

sticky bit
sticky bit

Reputation: 37467

You can use jsonb_build_object() to build a JSON from the parameters and append it to the value in the column. Also there's no need for dynamic SQL here just directly use an UPDATE statement.

...
UPDATE output_controlling_variables
       SET value = value || jsonb_build_object(test_id, test_name)
       WHERE id = 4;
...

Upvotes: 2

Related Questions