I have a column in a table extracted from json using json_extract_scalar(json_parse(<my json array>), '$.column')

Now I have a column in the table that is a varchar (I believe) and looks like:

I want to use this column later and put it in a nested JSON array, but if I put it into a json array as it is right now, it will look like:

"column":"word1, word2, word3, word4" in the json array, when I instead want:

"column": ["word1", "word2", "word3", "word4"]

So, my question is how do I make the column into a JSON array/list first so that the output is what I want when I later put it in an array?

I have tried doing CAST(column AS JSON) but it gives me:

I'm using Presto SQL

CodePudding user response：

CAST(SPLIT(json_extract_scalar(json_parse(<my json array>), '$.column'), ',') AS JSON) gave me the output I desired

CodePudding user response：

You need to convert you string into an array, for example using split:

select cast(split('word1, word2, word3, word4', ',') as json)

Output: