adding to an array in json column in Postgres

Question

I have a json object column in a Postgres table. Suppose each record contains the value for this column similar to the below json

{"country":"USA","states":["Texas","Alaska"]}

How would I create an update statement to add a new state "Virginia" to the state array in that json column.

table name : person-details column name : location

Answer 1

demo:db<>fiddle

Using jsonb_insert():

UPDATE nations
SET nation = jsonb_insert(nation::jsonb, '{states,0}', '"Virginia"')::json
WHERE nation ->> 'country' = 'USA';

If your data type is json, then you'll need the casts into type jsonb and back (as shown)