CODEWITHSUNDEEP
jsonpostgresqljsonb
ramesh
ramesh

Reputation: 17

update json object in postgresql having text field

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

Upvotes: 1

Views: 198

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 1

Related Questions