I'm attempting to parse json data from zendesk using v: structure

If you want to select all repeating elements you will need to use FLATTEN, otherwise you can use standard notation. This is all documented here: https://docs.snowflake.com/en/user-guide/querying-semistructured.html#retrieving-a-single-instance-of-a-repeating-element

So using this CTE to access the data in a way that look like a table:

with data(json) as (
    select parse_json(column1) from values
    ('{ 
    "id":151693, 
    "brand_id": 36000, 
    "created_at": "2022-0523T19:26:35Z", 
    "custom_fields": [
        { "id": 57866008, "value": false }, 
        { "id": 360022282754, "value": "" },
        { "id": 80814087, "value": "NC" } ], 
    "group_id": 36000770 
} ')
)

SQL to unpack the top level items, as you have shown you have working:

select
    json:id::number as id
    ,json:brand_id::number as brand_id
    ,try_to_timestamp(json:created_at::text, 'yyyy-mmddThh:mi:ssZ') as created_at
    ,json:custom_fields as custom_fields
from data;

gives:

So now how to tackle that json/array of custom_fields..

Well if you only ever have 3 values, and the order is always the same..

select
    to_array(json:custom_fields) as custom_fields_a
    ,custom_fields_a[0] as field_0
    ,custom_fields_a[1] as field_1
    ,custom_fields_a[2] as field_2
from data;

gives:

so we can use flatten to access those objects, which makes "more rows"

select
    d.json:id::number as id
    ,d.json:brand_id::number as brand_id
    ,try_to_timestamp(d.json:created_at::text, 'yyyy-mmddThh:mi:ssZ') as created_at
    ,f.*
from data as d
    ,table(flatten(input=>json:custom_fields)) f

So we can pull out know values (a manual PIVOT)

select
    d.json:id::number as id
    ,d.json:brand_id::number as brand_id
    ,try_to_timestamp(d.json:created_at::text, 'yyyy-mmddThh:mi:ssZ') as created_at
    ,max(iff(f.value:id=80814087, f.value:value::text, null)) as v80814087
    ,max(iff(f.value:id=360022282754, f.value:value::text, null)) as v360022282754
    ,max(iff(f.value:id=57866008, f.value:value::text, null)) as v57866008
from data as d
    ,table(flatten(input=>json:custom_fields)) f
group by 1,2,3, f.seq

grouping by the f.seq means if you have many "rows" of input these will be kept apart, even if they share common values for 1,2,3

gives:

Now if you do not know the names of the values, there is no way short of dynamic SQL and double parsing to turns rows into columns.

I ended up doing the following, with 2 different CTEs (CTE and UCF):

1. Used to_array to gather my custom fields
2. Unioned the custom fields together twice; once for the id of the field and once for the value (and used combinations of substring, position and replace to clean up data as needed (same setup for all fields)
3. Joined the resulting data to a Custom Fields Table (contains the id and a name) to include the name of the custom field in my result set.

WITH UCF AS (--Union Gathered Array into 2 fields (an id field and a value field) WITH CTE AS( ---Gather array of custom fields

SELECT v:id as id,
to_array(v:custom_fields) as cf
   ,cf[0] as f0,cf[1] as f1,cf[2] as f2
FROM ZD_TICKETS)

SELECT id,
substring(f0,7,position(',',f0)-7) AS cf_id,  REPLACE(substring(f0,position('value":',f0)+8,position('"',f0,position('value":',f0)+8)),'"}') AS cf_value
FROM CTE c
WHERE f0 not like '%null%'
UNION
SELECT id,
substring(f1,7,position(',',f1)-7) AS cf_id,
REPLACE(substring(f1,position('value":',f1)+8,position('"',f1,position('value":',f1)+8)),'"}') AS cf_value
FROM CTE c
WHERE f1 not like '%null%'
-- field 3
UNION
SELECT id,
substring(f2,7,position(',',f2)-7) AS cf_id,
REPLACE(substring(f2,position('value":',f2)+8,position('"',f2,position('value":',f2)+8)),'"}') AS cf_value
FROM CTE c
WHERE f2 not like '%null%' --this removes records where the value is null
)
SELECT UCF.*,CFD.name FROM UCF
LEFT OUTER JOIN "FLBUSINESS_DB"."STAGING"."FILE_ZD_CUSTOM_FIELD_IDS" CFD
 ON CFD.id=UCF.cf_id
 WHERE cf_value<>''  --this removes records where the value is blank

The result set looks like: