how to get the first tuple in a string column using presto-CodePudding

so i am having a column in the table, the data type of the column is varchar, but it contains an array of tuples, so what I need is to extract the first tuple of the array in the table

this is the original table

userid	comments
1	[["hello world",1],["How did you",1],[" this is the one",1]]
2	[["hello ",1],["How ",1],[" this",1]]

and this is what i am looking for , please notice that the datatype of 'comments' column is varchar.

userid	comments
1	hello world
2	hello

CodePudding user response：

json_extract_scalar should do the trick:

WITH dataset (userid, comments) AS (
    VALUES  (1, json '[["hello world",1],["How did you",1],[" this is the one",1]]'),
            (2, json '[["hello ",1],["How ",1],[" this",1]]')
)

--query
select userid,
    json_extract_scalar(comments, '$[0][0]')
from dataset

Output:

userid	comments
1	hello world
2	hello

Note that it will allow to extract only single value, if you want multiple values you will need to do some casting (similar to one done here but using arrays, for example array(json)).