how to update jsonb value if path key is in variable postgresql

Question

I have a jsonb field in the table. I want to update an individual key value, So I am using jsonb_set method. The key that I want to update is in a variable.

Here is the jsonb object that I want to update

{"yes":5,"total_votes":6,"no":1}

and the key variable vote_to is yes.

Here is how I am trying

update polls set result = jsonb_set(result, ('{'||vote_to||'}'),vote_count::text::jsonb) where id=_poll_id;

And the error is

ERROR:  function jsonb_set(jsonb, text, jsonb) does not exist
LINE 1: update polls set result = jsonb_set(result, ('{'||vote_to||'...
                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And how can I update two keys in one shot? And also vote_count is an integer so i need it to double cast to make jsonb

vote_count::text::jsonb

Is there any other way to do this?

Answer 1

According to the PostgreSQL documentation the signature of the jsonb_set function is jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]). So you have to cast the second argument to text[].

Using this :

CREATE TABLE polls (id INTEGER, result jsonb, vote_count jsonb);
INSERT INTO polls VALUES (1, '{"yes":5,"total_votes":6,"no":1}'::jsonb, '{"v": 1}'::jsonb);
CREATE OR REPLACE FUNCTION test(poll_id INTEGER, vote_to TEXT) RETURNS VOID AS 
$$
DECLARE
    to_update jsonb;
BEGIN
    update polls set result = jsonb_set(result, ('{'||vote_to||'}')::text[], vote_count) where id=poll_id;
END;
$$ LANGUAGE plpgsql;
SELECT test(1, 'yes');

You will get the following result for SELECT * FROM polls;:

 id |                    result                    | vote_count
----+----------------------------------------------+------------
  1 | {"no": 1, "yes": {"v": 1}, "total_votes": 6} | {"v": 1}
(1 row)