CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenaprestotrino
Luis 2023
Luis 2023

Reputation: 85

Trying to convert json to string (Athena AWS)

I have a json formatted like: myjson = {"key":["value1","value2"]} and a I want to convert it as string
Sometime this json can return null: myjson = {"key":null} .

I want to get the values as a string, but I get an error when I try to cast cast(json_extract(myjson,'$.key') as varchar). The error says that is not possible to convert '["value1","value2"]' to varchar. I think it is because of the double quote. I need help to work around this problem.

Edit1:

Upvotes: 5

Views: 6054

Answers (3)

Guru Stron
Guru Stron

Reputation: 142173

You can use json_format (though depending on the following usage leaving just json_extract without cast/format/etc. can be fine in some cases):

-- sample data
with dataset(json_str) as (
    values ('{"key":["value1","value2"]}'),
        ('{"key":null}')
)

-- query
select json_format(json_extract(json_str,'$.key'))
from dataset;

Output:

_col0
["value1","value2"]
null

Another option is casting the result of json_extract to array(varchar) and using array_join on it (see this answer for example).

Upvotes: 2

Luis 2023
Luis 2023

Reputation: 85

I found the following solution

cast(json_extract(json_str, '$.key') as array(varchar)) as array_values
array_join(array_values,',' )

With this a get a string like 'value1,value2' it is easier to work then '["value1","value2"]'

Upvotes: 1

Mehmet Güngören
Mehmet Güngören

Reputation: 2774

You can not cast list to string, but if you want you can join list items to string like.

array_join(json_extract(myjson,'$.key'), ' ')

Upvotes: 0

Related Questions