Parse JSON

Parsing JSON Strings

Description

Snowflake has a few helpful ways to deal w/ JSON objects. This snippet goes through a few ways to access elements in a JSON object:

SELECT 
   <json_column>:<level_1_element>.<level_2_element>.<level_3_element> -- select json element,
   <json_column>:"<level_1_element>"."<level_2_element>", -- select json element if element name has special characters or spaces
   <json_column>:<level_1_element>::<data_type>, -- cast return object to explicit type
   <json_column>:<level_1_array>[array_index], -- access one instance of array
   get(<json_column>:<level_1>,array_size(<json_column>:<level_1>)-1) -- get last element in array
FROM 
<TABLE>

where:

  • <json_column> is the column in <TABLE> with the JSON object
  • <level_n_element> is the level of nesting in the JSON object
  • <TABLE> is the table w/ the JSON data.

Example: