CODEWITHSUNDEEP
sqljsonpostgresqlsql-updatejsonb
hgb123
hgb123

Reputation: 14891

Update JSON-type column with jsonb_set()

I tried to update data column (with type of json) of books table by jsonb_set

UPDATE books 
SET data = jsonb_set(data, '{borrowers}', '[{"name":"john doe","time":"1:00PM"}]')
WHERE id= 'some_id';

And encountered this error:

> ERROR:  function jsonb_set(json, unknown, unknown) does not exist

I found that in the doc, jsonb_set only work with jsonb type

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

But I could not have privilege to update the column type to jsonb. So how could I resolve in this case?

Upvotes: 3

Views: 2075

Answers (1)

hgb123
hgb123

Reputation: 14891

Came up with a simple trick by type casting

UPDATE books 
SET data = jsonb_set(data::jsonb, '{borrowers}', '[{"name":"john doe","time":"1:00PM"}]')
WHERE id= 'some_id';

Upvotes: 4

Related Questions