1

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

column1column2
First["one", "two", "three"]
Second"one"
Third"third"
Third4

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';
klin's user avatar
klin
123k15 gold badges240 silver badges262 bronze badges
askedMay 16, 2022 at 22:31
Annie Hua's user avatar

2 Answers2

2

Use thejsonb_build_array() function:

update my_table set    column2 = jsonb_build_array(column2)where jsonb_typeof(column2) != 'array';

Test it indb<>fiddle.

Read about the functionin the documentation.

answeredMay 16, 2022 at 22:46
klin's user avatar
Sign up to request clarification or add additional context in comments.

Comments

0

One option is to use concatenation operators(||) to wrap up the expresion with square brackets along with casting toTEXT, then recasting toJSONB such as

UPDATE tab   SET column2 = ('['||column2::TEXT||']')::JSONB WHERE jsonb_typeof(column2) != 'array'

Demo

answeredMay 17, 2022 at 11:29
Barbaros Özhan's user avatar

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.