求助：如何在Snowflake中将列内嵌套JSON格式的非结构化数据拆分出accountNumber与orgUrns独立列

Hey there! I’ve run into this exact DynamoDB-style JSON format in Snowflake before—those nested S (string) and L (list) wrappers can be tricky at first, but once you map out the structure, it’s straightforward to extract the values you need.

Let’s break down your data structure first: your column holds a JSON array with a single object, which contains an M map. Inside that map, accountNumber is a string wrapped in S, and orgUrns is a list wrapped in L.

Basic Extraction (Single Element Array) #

Assuming your column is named raw_json_data and your table is your_table, here’s a SQL query to pull out accountNumber and orgUrns into separate columns:

SELECT
  -- Extract the account number string
  PARSE_JSON(raw_json_data)[0]:M:accountNumber:S AS account_number,
  -- Extract the orgUrns as an array
  PARSE_JSON(raw_json_data)[0]:M:orgUrns:L AS org_urns_array,
  -- Optional: Convert the array to a comma-separated string for easier reading
  ARRAY_TO_STRING(PARSE_JSON(raw_json_data)[0]:M:orgUrns:L, ', ') AS org_urns_string
FROM your_table;

Let me explain each part:

• PARSE_JSON(raw_json_data) converts your string column into a Snowflake JSON object.
• [0] grabs the first (and only, in your example) element from the outer array.
• :M accesses the nested map inside that element.
• :accountNumber:S drills down to the string value of accountNumber.
• :orgUrns:L pulls out the list of org URNs as a Snowflake array.

Handling Multiple Elements in the Array #

If your JSON array ever has more than one M object, use LATERAL FLATTEN to expand the array into individual rows:

SELECT
  flattened.value:M:accountNumber:S AS account_number,
  flattened.value:M:orgUrns:L AS org_urns_array,
  ARRAY_TO_STRING(flattened.value:M:orgUrns:L, ', ') AS org_urns_string
FROM your_table,
LATERAL FLATTEN(input => PARSE_JSON(raw_json_data)) AS flattened;

Handling NULL Values #

To avoid NULLs in your results if some entries are missing accountNumber or orgUrns, wrap the extractions in IFNULL:

SELECT
  IFNULL(PARSE_JSON(raw_json_data)[0]:M:accountNumber:S, 'No Account Number') AS account_number,
  IFNULL(PARSE_JSON(raw_json_data)[0]:M:orgUrns:L, ARRAY_CONSTRUCT()) AS org_urns_array
FROM your_table;

This should cover most scenarios—let me know if you run into edge cases with your specific data!

内容的提问来源于stack exchange，提问作者Nadeem