Step 2. Query Data¶
In this section, you explore SELECT statements to query the JSON data.
Retrieve
device_type
.SELECT src:device_type FROM raw_source;
The query return the following result:
+-----------------+ | SRC:DEVICE_TYPE | |-----------------| | "server" | +-----------------+
The query uses the
src:device_type
notation to specify the column name and the JSON element name to retrieve. This notation is similar to the familiar SQLtable.column
notation. Snowflake allows you to specify a sub-column within a parent column, which Snowflake dynamically derives from the schema definition embedded in the JSON data. For more information, refer to Querying Semi-structured Data.Note
The column name is case-insensitive, however JSON element names are case-sensitive.
Retrieve the
device_type
value without the quotes.The preceding query returns the JSON data value in quote. You can remove the quotes by casting the data to a specific data type, in this example a string.
This query also optionally assigns a name to the column using an alias.
SELECT src:device_type::string AS device_type FROM raw_source;
The query retuns the following result:
+-------------+ | DEVICE_TYPE | |-------------| | server | +-------------+
Retrieve repeating
f
keys nested within the array event objects.The sample JSON data includes
events
array. Each event object in the array has thef
field as shown.{ "device_type": "server", "events": [ { "f": 83, .. } { "f": 1000083, .. } ]}
To retrieve these nested keys, you can use the FLATTEN function. The function flattens the events into separate rows.
SELECT value:f::number FROM raw_source , LATERAL FLATTEN( INPUT => SRC:events );
The query returns the following result:
+-----------------+ | VALUE:F::NUMBER | |-----------------| | 83 | | 1000083 | +-----------------+
Note the
value
is one of the columns that FLATTEN function returns. The next step provides more details about using the FLATTEN function.
Next: Step 3. Flatten Data