Step 2. Query Data¶

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

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.
To remove the quotes around the data in your output, cast the data as another data type. In this case, we’ll 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 | +-------------+

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