CODEWITHSUNDEEP
snowflake-cloud-data-platform
xc2333
xc2333

Reputation: 99

How to select data from array of json object in snowflake

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;

Upvotes: 1

Views: 681

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

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

Upvotes: 2

Related Questions