'Postgres update jsonb string to array

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


Solution 1:[1]

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.

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 klin
Solution 2 Barbaros Özhan

Related Questions