Use Lateral Flatten To Extract Data from a Nested JSON in Snowflake
2 min readMay 10, 2023
Snowflake's LATERAL FLATTEN table function can convert semi-structured data to a relational representation.
For example, the subscriptions_variant table has two columns, route_id (primary key) and subscriptions (a variant column).
select * from subscriptions_variant;
+----------+------------------------+
| ROUTE_ID | SUBSCRIPTIONS |
|----------+------------------------|
| 1 | [ |
| | { |
| | "name": "free", |
| | "value": 10 |
| | }, |
| | { |
| | "name": "premium", |
| | "value": 20 |
| | }, |
| | { |
| | "name": "default", |
| | "value": 11 |
| | } |
| | ] |
| 2 | [ |
| | { |
| | "name": "free", |
| | "value": 20 |
| | }, |
| | { |
| | "name": "premium", |
| | "value": 30 |
| | }, |
| | { |
| | "name": "default", |
| | "value": 12 |
| | } |
| | ] |
| 3 | [ |
| | { |
| | "name": "free", |
| | "value": 30 |
| | }, |
| | { |
| | "name": "premium", |
| | "value": 40 |
| | }, |
| | { |
| | "name": "default", |
| | "value": 13 |
| | } |
| | ] |
+----------+------------------------+
3 Row(s) produced. Time Elapsed: 0.597sSnowflake’s LATERAL FLATTEN clause comes in handy to divide subscriptions JSON into distinct rows. The LATERAL keyword specifies that you want to join the result of the FLATTEN function with the subscriptions_variant table.
LATERAL FLATTEN can transform semi-structured data into a tabular format, making it easier to work with SQL queries.
select
route_id,
to_char( value:name) as name,
to_number(value:value) as value
from subscriptions_variant, lateral flatten(input => subscriptions);
+----------+---------+-------+
| ROUTE_ID | NAME | VALUE |
|----------+---------+-------|
| 1 | free | 10 |
| 1 | premium | 20 |
| 1 | default | 11 |
| 2 | free | 20 |
| 2 | premium | 30 |
| 2 | default | 12 |
| 3 | free | 30 |
| 3 | premium | 40 |
| 3 | default | 13 |
+----------+---------+-------+