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