Update integer json object to become an array with one integer

Question

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

Your array constructor should be array['query', 'category']. The way you have written it, it's just a one element array. You could also use '{query, category}'. — Jeremy
– Jeremy, Commented Dec 10, 2019 at 13:12

S-Man · Accepted Answer · 2019-12-10 13:40:14Z

1

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;

edited Dec 10, 2019 at 13:40

answered Dec 10, 2019 at 13:08

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

S-Man Over a year ago

Is there a reason why you removed the accept and upvote? Is there anything wrong? Should something be changed?

Collectives™ on Stack Overflow

Update integer json object to become an array with one integer

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