Step 2. Query Data

.

Step List

• Prerequisites

• JSON Data Partitioning

• Step 1. Copy Data Into the Target Table

• Step 2. Query Data

• Step 3. Flatten Data

• Step 4. Update Data

• Step 5. Congratulations!

Let’s query the JSON data in the SRC column.

1. Start with querying the device type:

   SELECT src:device_type
     FROM raw_source;
   
   +-----------------+
   | SRC:DEVICE_TYPE |
   |-----------------|
   | "server"        |
   +-----------------+
   

   Where:

   src

   The column name in the RAW_SOURCE table.

   device_type

   An element in the JSON schema.

   src:device_type

   Notation that indicates which element in the SRC column to select. This notation is similar to the familiar SQL table.column notation. Snowflake allows you to effectively specify a column within the column (i.e., a sub-column), which is dynamically derived based on the schema definition embedded in the JSON data.

   Note

   The column name is case-insensitive but element names, or keys, are case-sensitive.

2. To remove the quotes around the data in your output, cast the data as another data type. In this case, you can cast the value as a string.

   At the same time, you can give the column an alias, as you would any normal column:

   SELECT src:device_type::string AS device_type
     FROM raw_source;
   
   +-------------+
   | DEVICE_TYPE |
   |-------------|
   | server      |
   +-------------+
   

3. You can query nested key-values by flattening the values.

   Let’s query the repeating f value in the sample data. This repeating value is nested within event objects:

   {
    "device_type": "server",
    "events": [
      {
        "f": 83,
        ..
      }
      {
        "f": 1000083,
        ..
      }
    ]}
   

   The SELECT statement includes the FLATTEN function:

   SELECT
     value:f::number
     FROM
       raw_source
     , LATERAL FLATTEN( INPUT => SRC:events );
   
   +-----------------+
   | VALUE:F::NUMBER |
   |-----------------|
   |              83 |
   |         1000083 |
   +-----------------+
   

   Learn more about the FLATTEN function in the next step.

Next: Step 3. Flatten Data