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

S-Man · Accepted Answer · 2019-09-24 11:29:06Z

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)

answered Sep 24, 2019 at 11:29

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

5 Comments

Akhil Job Over a year ago

What if I want to add a value from the same json to an array in the same json.

S-Man Over a year ago

Can you give an example?

Akhil Job Over a year ago

sample json : {"country":"USA","states":["Texas","Alaska"], "other":"NewYork"} how would I update the value of states to contain the value present in other as well.

S-Man Over a year ago

you want to get "new york" into the array?

Akhil Job Over a year ago

I want to get the string whatever text is present in "other" to be addded to the states array. In this example , "New York"

Collectives™ on Stack Overflow

adding to an array in json column in Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related