CODEWITHSUNDEEP
postgresql
Pranav Unde
Pranav Unde

Reputation: 193

Curly braces missing after replace json data

Following data in table dashboard_data. The column name is mr_data.

{"priority_id": "123", "urgent_problem_id": "111", "important_problem_id": "222"}
{"priority_id": "456", "urgent_problem_id": "", "important_problem_id": "333"}
{"priority_id": "789", "urgent_problem_id": "444", "important_problem_id": ""}

Query-

UPDATE
    dashboard_data
SET
    mr_data = replace(dashboard_data.mr_data,'urgent_problem_id','urgent_problem_ids')
WHERE
    mr_data->>'urgent_problem_id' IS NOT NULL;

Expected result:

{"priority_id": "123", "urgent_problem_ids": {"111"}, "important_problem_ids": {"222"}}
{"priority_id": "456", "urgent_problem_ids": {""}, "important_problem_ids": {"333"}}
{"priority_id": "789", "urgent_problem_ids": {"444"}, "important_problem_ids": {""}}

Is there any way that during replace we get {} representation of data as shown in expected result.

Upvotes: 0

Views: 250

Answers (1)

user330315
user330315

Reputation:

Assuming Postgres 9.5 or newer.

You can use jsonb_set to add a proper JSON array with a new key, then remove the old key from the JSON (which is the only way to rename a key)

update dashboard_data
  set mr_data = jsonb_set(mr_data, '{urgent_problem_ids}', 
                          jsonb_build_array(mr_data -> 'urgent_problem_id'), true) 
                - 'urgent_problem_id'
where mr_data ? 'urgent_problem_id';

jsonb_build_array(mr_data -> 'urgent_problem_id') creates a proper JSON array with the (single) value from the urgent_problem_id that value is then stored under the new key urgent_problem_ids and finally the old key urgent_problem_id is removed using the - operator.

Online example: http://rextester.com/POG52716

If your column is not a JSONB (which it should be) then you need to cast the column inside jsonb_set() and cast the result back to a json

Upvotes: 1

Related Questions