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

Taylor Wood · Accepted Answer · 2017-12-03 14:58:24Z

8

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

edited Dec 3, 2017 at 14:58

answered Dec 3, 2017 at 14:52

Taylor Wood

16.2k1 gold badge24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rocksteady Over a year ago

In case this post comes up as a search result for anyone else, the error in my case was that I was trying to build the path (2nd argument) dynamically, using a column from another table. My mistake was not converting it to a TEXT array (::TEXT[]).

Collectives™ on Stack Overflow

How to update a jsonb column and put text array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related