Kontext Kontext

Snowflake - Flatten nested JSON array

event 2023-06-14 visibility 259 comment 0 insights
more_vert
insights Stats

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);
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts