Extracting values from an array using snowflake

So with a CTE to provide fake data, and parse the JSON for us:

WITH fake_data AS (
    SELECT parse_json(column1) as json
    FROM VALUES 
    ('[
    {
      "arrival_date": "2022-02-15T08:00:00.000Z",
      "cargo_available_timestamp": "2022-02-16T13:00:00.000Z",
      "cargo_type": "unable_to_provide",
      "carton_count": null,
      "lfd": "2022-02-17T08:00:00.000Z"
    },
    {
      "arrival_date": "2022-02-16T08:00:00.000Z",
      "flight_status": "in_transit",
      "flight_status_other": null
    }
     ]')
 )
 select 
    json[0] as array_0
    ,json[1] as array_1
    ,array_0:lfd as lfd_0
    ,array_1:lfd as lfd_1
 from fake_data;

we get:

Thus if you know you JSON array will always be in order you can use:

 select 
    json[0]:lfd as lfd
    ,to_timestamp_ntz(lfd) as lfd_as_timestamp
 from fake_data;

Now if you are not always sure the order of you array, or you need to pick an array element, you will want to FLATTEN the array.

WITH fake_data AS (
    SELECT parse_json(column1) as json
    FROM VALUES 
    ('[
    {
      "arrival_date": "2022-02-15T08:00:00.000Z",
      "cargo_available_timestamp": "2022-02-16T13:00:00.000Z",
      "cargo_type": "unable_to_provide",
      "carton_count": null,
      "lfd": "2022-02-17T08:00:00.000Z"
    },
    {
      "arrival_date": "2022-02-16T08:00:00.000Z",
      "flight_status": "in_transit",
      "flight_status_other": null
    }
     ]')
     ,('[
       {
      "arrival_date": "2022-02-16T08:00:00.000Z",
      "flight_status": "in_transit",
      "flight_status_other": null
    },
    {
      "arrival_date": "2022-02-15T08:00:00.000Z",
      "cargo_available_timestamp": "2022-02-16T13:00:00.000Z",
      "cargo_type": "unable_to_provide",
      "carton_count": null,
      "lfd": "2022-02-18T08:00:00.000Z"
    }
     ]')
 )
 select 
    to_timestamp_ntz(f.value:lfd) as lfd_ntz
 from fake_data d, table(flatten(input=>d.json)) f
 where lfd_ntz is not null;