How to select data from array of json object in snowflake

user_7922131 0
Viewed 78

Data I have:

ID Value
1 [{"code": "SM", "place": "San Mateo, CA, USA"},{"code": "IND", "place": "Indianapolis, IN, USA"}]

What I want it to be transformed as:

ID Value
1 San Mateo, CA, USA; Indianapolis, IN, USA

This is as far as I can get (table is just a sample)

SELECT 
INDEX, 
PARSE_JSON(f.THIS),
ARRAY_TO_STRING(PARSE_JSON(f.THIS),';')
FROM TABLE(FLATTEN(input => parse_json('[{"code": "SM","place": "San Mateo, CA, USA"},{"code": "IND","place": "Indianapolis, IN, USA"}]'))) f LIMIT 1;
snowflake-cloud-data-platform
1 Answers

You're close:

SELECT 
seq, 
listagg(f.value:place, '; ')
FROM TABLE(FLATTEN(input => parse_json('[{"code": "SM","place": "San Mateo, CA, USA"},{"code": "IND","place": "Indianapolis, IN, USA"}]'))) f 
group by seq

-- San Mateo, CA, USA; Indianapolis, IN, USA
user_1324380
Related