CODEWITHSUNDEEP
sqljsonpostgresql
Akhil Job
Akhil Job

Reputation: 419

adding to an array in json column in Postgres

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

Upvotes: 1

Views: 90

Answers (1)

S-Man
S-Man

Reputation: 23696

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)

Upvotes: 1

Related Questions