Parsing a JSON that has an array in one of the values in Snowflake-CodePudding

I have a variant column in tableA which has JSON data. I am trying to parse the data and insert to another table. When I try to parse an array value, am not able to get the array brackets out.

Table A: |response | |---------------------------------------------------| |{"survey_Id": "100","values": {"survey_Date": "10/19/2021","survey_TEXT": "Speech Language Pathologist","survey_options_selected": ["EMAIL"],"Time": "4:00 PM"}} | |{"survey_Id": "101","values": {"survey_Date": "10/20/2021","survey_TEXT": "Nurse","survey_options_selected": ["SMS","EMAIL","PHONE"],"Time": "5:00 PM"}} |

Snowflake query:

SELECT 
response:survey_id::varchar, 
response:values:survey_date::varchar, 
response:values:survey_options_selected::varchar, 
response:values:Time::varchar
from tableA

The column survey_options_selected is returning the value in an array format

["EMAIL]
["SMS","EMAIL","PHONE"]

instead of the below expected output.

Expected output:

survey_Id	survey_Date	survey_options_selected	Time
100	10/19/2021	EMAIL	4:00 PM
101	10/20/2021	SMS, EMAIL, PHONE	5:00 PM

Could you please tell me what i can change to get the output in the desired format?

CodePudding user response：

Use ARRAY_TO_STRING with a separator as comma.