Snowflake Array to String issue

Question

In Snowflake database, I'm trying to extract string from an array column.

The name of the column in the table is: mbus.

So, if you query the table:

select PRO.JSON_DATA:mbus 
FROM SOURCE_TABLE1 PRO

the result will be:

[{"region":"EAME"},{"region":"LA"},[{"region":"NA"},[{"region":"NAP"},[{"region":"SAP"}]

I'm using ARRAY_TO_STRING function:

SELECT ARRAY_TO_STRING(PRO.JSON_DATA:mbus:region, ', ') 
FROM SOURCE_TABLE1 PRO

, but the result is NULL.

The final result should be: EAME, LA, NA, NAP, SAP (Extracting from the column).

Could you help me on this one? I need to build a query to extract the properly strings from the array.

Answer 1

Using FLATTEN to transform json to rows and LISTAGG to combine back to single string:

SELECT LISTAGG(f.value:region::STRING, ',') AS col
FROM SOURCE_TABLE1 PRO
,LATERAL FLATTEN(input => PRO.JSON_DATE:mbus) f