# Step 2. Query Data¶

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

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, 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      |
+-------------+

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 |
+-----------------+