Postgres update jsonb string to array-CodePudding

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';

CodePudding user response：

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.

CodePudding user response：

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'

Demo