Snowflake - Flatten nested JSON array

Code description

Snowflake provides a number of JSON related functions to convert string (varchar) to JSON object and extract JSON values from the object or flatten nested array, etc. 

The code snippet shows an example of flattening the following JSON string using lateral flatten:

{
   "a":"a",
   "b":[
      {
         "c":"c1",
         "d":[
            1,
            2,
            3,
            4,
            5,
            6,
            7
         ]
      },
      {
         "c":"c2",
         "d":[
            10,
            20,
            30,
            40,
            50,
            60,
            70
         ]
      }
   ]
}

Code snippet

WITH str AS
(
SELECT '{
   "a":"a",
   "b":[
      {
         "c":"c1",
         "d":[
            1,
            2,
            3,
            4,
            5,
            6,
            7
         ]
      },
      {
         "c":"c2",
         "d":[
            10,
            20,
            30,
            40,
            50,
            60,
            70
         ]
      }
   ]
}' AS json_str),
obj as 
(
SELECT parse_json(json_str) as json_obj from str
),
l1 as (
SELECT 
json_obj:a::STRING as a,
VALUE:c::string as c,
VALUE:d as d_array
FROM obj, LATERAL FLATTEN(INPUT=> json_obj:b)
)
SELECT a, c, VALUE as d
FROM l1, LATERAL FLATTEN(INPUT => d_array);