Question 114086 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Append a value in Json field of a postgres table

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0
    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search