Question: PostgreSQL jsonb_set not working


PostgreSQL jsonb_set not working

Answers 1
Added at 2016-12-23 01:12

update table set json = jsonb_set(json, '{field}', array_to_json(array_append(array(select * from jsonb_array_elements_text(json->'array')), '1')), true) where id = 22;

The following query is not working for me but it works if i get the end result of array_to_json(array_append(array(select * from jsonb_array_elements_text(json->'array')), '1')) and use that instead.

for example update table set json = jsonb_set(json, '{field}', '['1', '2']', true) where id = 22; works but the abover query does not i get an error that says function jsonb_set(jsonb, unknown, json, boolean)

I have no clue why the target type is returning unknown.

Thanks for help in advance

Answers to

PostgreSQL jsonb_set not working

nr: #1 dodano: 2016-12-23 02:12

array_to_json() is of type json while you need jsonb as the third argument for jsonb_set(). There is no implicit casting from json to jsonb so you have to cast manually:

update the_table
set json = 
                array(select * from jsonb_array_elements_text(json->'array'))
                , '1'
        )::jsonb, --- add this cast
where id = 22;
Source Show
◀ Wstecz