Insert and parse array and dictionary objects in Snowflake table

Snowflake has the native ability to handle data structures such as arrays and dictionaries. Let’s learn how to Insert and parse array and dictionary objects in Snowflake table. In the modern data landscape, JSON is a very common format of transporting data between applications. JSON is simply a standardized text format to store arrays and dictionaries of values. In JSON format, arrays are denoted by [ ] and dictionaries are denoted by { } .

Insert and parse array and dictionary objects in Snowflake table

Why are arrays and dictionary objects important in the modern data landscape?

Most modern applications, especially software-as-a-service applications, offer a way to extract data out of their systems. Most of the time an application programming interface, most commonly referred to in its abbreviated form: API, is the mechanism in which data is extracted from 3rd party systems. APIs act as an intermediary between two different systems. APIs allow controlled access to data and the operational systems that run an application and generate data. In today’s engineering landscape, API data is almost always returned in JSON format. A modern data warehouse should have a robust feature set to handle this type of semi-structured data, as it’s the most commonly seen data format on the internet. Fortunately for us, Snowflake makes handling arrays and dictionaries easy.

Create a table with an array and dictionary object

Snowflake has two functions: array_construct() and object_construct() . These functions are used to create (aka “construct”) array and dictionary objects.

Here’s an example of creating a table with an array and a dictionary.

create table object_table as

select 
    ARRAY_CONSTRUCT(0, 1, 2) as array, 
    OBJECT_CONSTRUCT('key', 'value', 'key2', 'value', 'query',
                     OBJECT_CONSTRUCT('field1', 'one', 'field2', 
                                      ARRAY_CONSTRUCT(0,1,2
                                                     )
                                     ),
                     'meta', 
                     ARRAY_CONSTRUCT(3, 4, 5)
                    ) as dict

Notice that the dictionary object has nested objects and also has arrays as objects within the dictionary. This is why we call arrays and dictionaries “semi-structured”. Arrays and dictionaries are flexible because there are no predefined fields or structure. The structure is more flexible that way. But, if you have several JSON records and each of them have slightly different structures, they cannot be easily structured for analytics.

Insert into an existing table from a raw JSON array and dictionary object

Sometimes JSON needs to be inserted into a table, from raw JSON. Here’s an example of inserting the same record as the query above into the same table created in the previous query, but from raw JSON, rather than using the construct functions:

insert into object_table 
    select 
        parse_json($1),
        parse_json($2)

    from
        values
        (
            '[0,1,2]',
            '{ "key": "value", 
               "key2": "value", 
               "query": {
                   "field1": "one", 
                   "field2": [0,1,2]
               }, 
               "meta": [3,4,5] 
             }' 
        );

Before semi-structured data is analytics ready, in most analytics use cases, you should parse out and structure the data into a traditional table with rows and columns. Parsing and structuring semi-structured data is a very common data engineering task. Luckily, Snowflake provides features that make this a relatively simple task, albeit a tedious one when a large number of data fields need to be parsed out from semi-structured data.

Querying arrays and dictionary objects

Here’s an example of how to parse out individual fields from semi-structured data into individual columns:

select 
   array[0]::integer as array_index_0, 
   array[1]::integer as array_index_1, 
   array[2]::integer as array_index_2,
   dict:key::varchar as key,
   dict:key2::varchar as key2,
   dict:query.field1::varchar as query_field_1,
   dict:query.field2[0]::integer as query_field_2_index_0,
   dict:query.field2[1]::integer as query_field_2_index_1,
   dict:query.field2[2]::integer as query_field_2_index_2,
   dict:meta as meta_array

from
   object_table

Conclusion

In this article we’ve addressed how to insert and parse array and dictionary objects in Snowflake table, and covered. We’ve also learned how to query arrays and objects. With Datameer, you can easily parse out semi-structured data using the formula builder. Datameer is a great tool to handle the task of parsing and structuring semi-structured data into analytics ready data. Give Datameer a try today with its free trial