Trying to convert json to string (Athena AWS)

Question

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:

• The output of json_extract(myjson,'$.key') is a json object;
• I want to get a string like 'value1, value2' or 'null'
• At the moment I do not know how to verify if it is null or not

Answer 1

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).