Question: PostgreSQL jsonb_set not working

Question

PostgreSQL jsonb_set not working

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

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
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 = 
    jsonb_set(
        json, 
        '{field}', 
        array_to_json(
            array_append(
                array(select * from jsonb_array_elements_text(json->'array'))
                , '1'
            )
        )::jsonb, --- add this cast
    true) 
where id = 22;
Source Show
◀ Wstecz