CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.5
user13641365
user13641365

Reputation:

Update the value of JSON elements in Postgresql

I have table with following table structure:

create table instances(
                       id               bigint, 
                       createdate       timestamp, 
                       createdby        bigint, 
                       lastmodifieddate timestamp, 
                       lastmodifiedby   bigint, 
                       context          text
                       )

Field context contains a JSON data i.e.

insert into instances values 
(1, '2020-06-01 22:10:04', 20112,'2020-06-01 22:10:04',20112, 
    '{"id":1,"details":[{"binduserid":90182}]}')

I need to replace all values of JSON element binduserid with value 90182 using postgres query.

I have achieved this by using REPLACE function:

update instances 
   set context = replace(context, '"binduserid":90182','"binduserid":1000619')

Is there any other way to do this by using Postgres JSON Functions

Upvotes: 1

Views: 92

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Firstly, let's consider storing the column as JSON or JSONB those are already defined to hold the data properly and use in a productive manner such as no needed conversions among types like holding a DATE value in DATE format rather than a STRING.

In this case I consider context column in JSONB data type.

You can use JSONB_SET() function in order to get the desired result where the first argument(target) might be in array format through use of JSONB_BUILD_ARRAY() function with indexes (as 0 in '{0,details}' for this case ) to manipulate easily by the below DML Statement :

UPDATE instances
   SET context = 
          JSONB_SET(JSONB_BUILD_ARRAY(context), '{0,details}','[{"binduserid":1000619}]')

Demo

Upvotes: 2

Related Questions