Step 2. Query Data

In this section, you explore SELECT statements to query the JSON data.

  1. Retrieve device_type.

    SELECT src:device_type
      FROM raw_source;
    
    Copy

    The query return the following result:

    +-----------------+
    | SRC:DEVICE_TYPE |
    |-----------------|
    | "server"        |
    +-----------------+
    
    Copy

    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 SQL table.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.

  2. 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;
    
    Copy

    The query retuns the following result:

    +-------------+
    | DEVICE_TYPE |
    |-------------|
    | server      |
    +-------------+
    
    Copy
  3. Retrieve repeating f keys nested within the array event objects.

    The sample JSON data includes events array. Each event object in the array has the f field as shown.

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

    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 );
    
    Copy

    The query returns the following result:

    +-----------------+
    | VALUE:F::NUMBER |
    |-----------------|
    |              83 |
    |         1000083 |
    +-----------------+
    
    Copy

    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