I have a table where column2 is type JSONB and I would like to alter the values where column2 is a string to an array. I would like the result for column2 to have "one" be ["one"] and "third" be ["third"]
Table
| column1 | column2 |
|---|---|
| First | ["one", "two", "three"] |
| Second | "one" |
| Third | "third" |
| Third | 4 |
How should I be updating the value? Here's what I have tried:
UPDATE table
SET columnn2 = ARRAY[value]::JSONB
WHERE jsonb_typeof(column2)!='array';
Use the jsonb_build_array() function:
update my_table set
column2 = jsonb_build_array(column2)
where jsonb_typeof(column2) != 'array';
Test it in db<>fiddle.
Read about the function in the documentation.
One option is to use concatenation operators(||) to wrap up the expresion with square brackets along with casting to TEXT, then recasting to JSONB such as
UPDATE tab
SET column2 = ('['||column2::TEXT||']')::JSONB
WHERE jsonb_typeof(column2) != 'array'
