I trying to append a JSON value in the Postgres table

Table structure

id | name | field
------------------------------------
1  | jack |{age:22, place:"london"}
2  | rocky|{age:34, place:"nyc"}

for the first records, I'm appending the value "NYC" to the existing value(example: {age:22, place:"London,NYC"} and for the second record, I'm replacing the existing value "nyc" to "SFO"

update table
set field->>place = field->>place   "NYC"
where id =1

update table
set field->>place = "SFO"
where id =2

but the first record is not getting updated.

CodePudding user response：

You may use the || operator and jsonb_build_object.

update the_table 
set field = field || jsonb_build_object('place', (field->>'place')||',NYC')
where id = 1;

update the_table 
set field = field || jsonb_build_object('place', 'SFO')
where id = 2;

NB: Postgres 9.5
DB-fiddle

CodePudding user response：

1. operator never existed in PostreSQL for json or jsonb types. You might've had || in mind, for string concatenation which in some languages is a .
2. When issuing an update you need to address a field. By using ->> operator to the left of =, you're addressing a value extracted from the field instead, which breaks the syntax.
3. You forgot a comma before adding 'NYC' to 'London'.
4. PostgreSQL versions before 10 are no longer supported, you're encouraged upgrade. On November 10, 2022, version 10 will no longer be supported either.

That being said, this works on 9.3:

update test
set field = concat( '{"place":"',
                    field->>'place',
                    ',NYC"}'
                  )::json
where id =1;

update test
set field = '{"place":"SFO"}'::json
where id =2;