How to update a jsonb column and put text array

Question

I have jsonb column like so:

{name: "Toby", occupation: "Software Engineer", interests: ""}

Now, I need to update the row and put a text array like ['Volleyball', 'Football', 'Swim'] into interests field. What I've tried so far:

UPDATE users SET data = jsonb_set(data, '{interests}', ARRAY['Volleyball', 'Football', 'Swim'], true) WHERE id=84;

data is the jsonb column

But it returns an error:

ERROR: function jsonb_set(jsonb, unknown, integer[], boolean) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

P.S:

I'm using PostgreSQL 10

Answer 1

The third argument needs to be of JSONB type too.

UPDATE users SET data = jsonb_set(data, '{interests}', '["Volleyball", "Football", "Swim"]'::jsonb, true) WHERE id=84;

This will also work, which is a little closer to your example using ARRAY:

UPDATE users SET data = jsonb_set(data, '{interests}', to_jsonb(array['Volleyball', 'Football', 'Swim']), true) WHERE id=84