update json object in postgresql having text field

Question

I had a column in PostgreSQL which has text datatype (data text)

the data in this column will be like

[
 {"code":"ABC","Value":"A1"},
 {"code":"Name","Value":"Ramesh"},
 {"code":"Age","paramValue":"24"}
]

in the above json I want to update value such as {"code":"Age","paramValue":"30"}

the data would be like

[
 {"code":"ABC","Value":"A1"},
 {"code":"Name","Value":"Ramesh"},
 {"code":"Age","paramValue":"30"}
]

since it TEXT field I tries different ways but couldn't succeed

I tried selecting first

SELECT id, data ::json->'"code":"ABC"' as name 
  FROM my_table;

I am getting error ERROR: invalid input syntax for type json DETAIL: Expected "," or "}", but found

Barbaros Özhan · Accepted Answer · 2021-06-14 19:46:46Z

1

Try below Query:

with cte as (
             select id, '{'||b-1||', paramValue}' "path_" 
               from test, 
                    jsonb_array_elements(data::jsonb) with ordinality t(a,b)
              where a->>'code'='Age'
)
update test t1 
   set data=jsonb_set(t1.data::jsonb,path_::text[],'"30"'::jsonb) 
  from cte 
 where t1.id=cte.id

Note: Above solution will only if value in field data is proper JSON and there is only one object having 'code' as Age per array{"code":"Age","paramValue":"30"}

DEMO

edited Jun 14, 2021 at 19:46

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jun 13, 2021 at 8:10

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

update json object in postgresql having text field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related