I'm having trouble casting a structured VARCHAR to a nested array in Snowflake.
Each VARCHAR has the format:
[['item1a', 'item1b', 'item1c', 'item1d'], ['item2a', 'item2b', 'item2c', 'item2d'], ..., ['itemNa', 'itemNb', 'itemNc', 'itemNd']]
where there are N arbitrary arrays (all of length 4) in the outer array.
Can someone give me a hint on the SQL I'd used for this? I'd also ideally like to get rid of the single quotes surrounding each element.
ARRAY_CONSTRUCT is the normal way to make static arrays.
SELECT
ARRAY_CONSTRUCT('item1a', 'item1b', 'item1c', 'item1d') as array1,
ARRAY_CONSTRUCT('item2a', 'item2b', 'item2c', 'item2d') as array2,
ARRAY_CONSTRUCT(array1, array1) as array_array_1,
ARRAY_CONSTRUCT(ARRAY_CONSTRUCT('item1a', 'item1b', 'item1c', 'item1d'), ARRAY_CONSTRUCT('item2a', 'item2b', 'item2c', 'item2d')) as array_array_1;
gives:
| ARRAY1 | ARRAY2 | ARRAY_ARRAY_1 | ARRAY_ARRAY_1 |
|---|---|---|---|
| [ "item1a", "item1b", "item1c", "item1d" ] | [ "item2a", "item2b", "item2c", "item2d" ] | [ [ "item1a", "item1b", "item1c", "item1d" ], [ "item1a", "item1b", "item1c", "item1d" ] ] | [ [ "item1a", "item1b", "item1c", "item1d" ], [ "item2a", "item2b", "item2c", "item2d" ] ] |
OR
you can build them from data dynamically via ARRAY_AGG:
SELECT
outer,
ARRAY_AGG(val) WITHIN GROUP ( ORDER BY inner ) as array
FROM (
SELECT * from values
(1, 1, 'item1a'),
(1, 2, 'item1b'),
(1, 3, 'item1c'),
(2, 1, 'item2a'),
(2, 2, 'item2b'),
(2, 3, 'item2c')
v(outer,inner, val)
)
GROUP BY 1 ORDER BY 1;
gives:
| OUTER | ARRAY |
|---|---|
| 1 | [ "item1a", "item1b", "item1c" ] |
| 2 | [ "item2a", "item2b", "item2c" ] |
and then wrapped up again:
SELECT ARRAY_AGG(array) WITHIN GROUP ( ORDER BY outer )
FROM (
SELECT
outer,
ARRAY_AGG(val) WITHIN GROUP ( ORDER BY inner ) as array
FROM (
SELECT * from values
(1, 1, 'item1a'),
(1, 2, 'item1b'),
(1, 3, 'item1c'),
(2, 1, 'item2a'),
(2, 2, 'item2b'),
(2, 3, 'item2c')
v(outer,inner, val)
)
GROUP BY 1
);
gives:
| ARRAY_AGG(ARRAY) WITHIN GROUP ( ORDER BY OUTER ) |
|---|
| [ [ "item1a", "item1b", "item1c" ], [ "item2a", "item2b", "item2c" ] ] |
