arraysize in for loop

Help
1

The problem I’m having

HI,
I am trying to read json data in a table column. I got struck while reading data from an array.

json_column
“abbs”
“asdad”
“asda”
“ds”
“sdss”
array1:
0: “12121”
1: “12312”
2: “54464”

the code I have written is
select
json_column:array1[0]
from table.json_column.

What I am trying to achieve
select
{% for i in range( array_size) %}
json_column:array1[{{i}}]::number as values
from table.json_column

anybody can help on how to use the size of array in for loop??

Thanks

2

which database is this?

Note: @ originally posted this reply in Slack. It might not have transferred perfectly.

3

Hi,
dbt connected to Snowflake.

4

I would try to create sql in SF first.
select
index, key, value
from table
,LATERAL FLATTEN(input => table:json_column); .
From there in index 6 or so you would have value as array [ “12121”,“12312”,“54464”].
Next you just need to manipulate it in snowflake.
I do not think you needs ninja macros for this task especially if you do not know size of array.