CODEWITHSUNDEEP
jsonpostgresqlsql-updatepsql
nimgwfc
nimgwfc

Reputation: 1509

Update integer json object to become an array with one integer

I'm currently working with psql and trying to update an embedded json object to change a value from an integer to an array with one value (this is due to a function changing elsewhere).

My table looks like this:

id | dashboard_settings
-----------------------
 1 | {"query": {"year_end": 2018, "year_start": 2015, "category": 4}}

However I want the category bit to change to [4], like below:

id | dashboard_settings
-----------------------
 1 | {"query": {"year_end": 2018, "year_start": 2015, "category": [4]}}

So far, I have this which allows me to access the category section but I can't figure out how to turn it to an array:

UPDATE table
SET dashboard_settings = jsonb_set(
    dashboard_settings::jsonb, 
    array['query, category'], 
    to_jsonb(dashboard_settings->'query' ->'category'));

I've tried to concatenate and cast as an array but had no success

Upvotes: 1

Views: 47

Answers (1)

S-Man
S-Man

Reputation: 23726

Click: demo:db<>fiddle

Instead of to_jsonb() you could use jsonb_build_array():

UPDATE mytable
SET dashboard = s.out
FROM (
    SELECT
        jsonb_set(
            dashboard, 
            '{query, category}', 
            jsonb_build_array(dashboard -> 'query' -> 'category')
        ) AS out
    FROM mytable
    WHERE id = 1;
) s
WHERE id = 1;

Upvotes: 1

Related Questions