Step 2. Query Data

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

  1. 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.

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

    Learn more about the FLATTEN function in the next step.

Next: Step 3. Flatten Data