Updated varchar column data to jsonb data

Admin 1
Viewed 78

I'm trying to update a column from a varchar type column into a JSON but the column is already filled in with varchars how can I cast them to turn into a JSON with additional information.

Table structure with data:

-----+--------+
| id  | value  |
+-----+--------+
| 1   | value1 |
| 2   | value2 |
| 3   | value3 |
| 4   | value4 |
| 5   | value5 |
| 6   | value6 |
+-----+--------*

Expected Result:

+-----+----------------------------------+
| id  |             value                |
+-----+----------------------------------+
| 1   | {"t1": "val", "value": "value1"} |
| 2   | {"t1": "val", "value": "value2"} |
| 3   | {"t1": "val", "value": "value3"} |
| 4   | {"t1": "val", "value": "value4"} |
| 5   | {"t1": "val", "value": "value5"} |
| 6   | {"t1": "val", "value": "value5"} |
+-----+----------------------------------*

Kindly help me to resolve this query

postgresql
1 Answers

demo:db<>fiddle

When altering a column type you can add an expression to convert the data from the old type into the new one. You have to add the USING clause in order to do so:

ALTER TABLE mytable
ALTER COLUMN "value" TYPE json USING json_build_object('t1', 'val', 'value', "value");

In this case use the json_build_object() function to create the expected JSON object incl. the old value

user_39842210
Related