1

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

Improve this question
1
  • 1
    Why are you storing json as text instead of json? Commented Jun 1, 2020 at 18:13

1 Answer 1

Reset to default
2

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.