Nested Game of Thrones Data - Snowflake + R

nested_got_data.R

library(tidyverse)
library(repurrrsive) # game of thromes dataset
library(listviewer)  # jsonedit for pretty viewing

jsonedit(got_chars, mode = "view")

really_nested <- tibble(id = 1:30, nested_stuff = got_chars)

really_nested %>%
  hoist(
    nested_stuff,
    name = list("name"),
    primary_title = list("titles", 1)
  )

nested_got_data.sql

-- in Snowflake:
select id
  , nested_stuff:name::string as name -- end with double-colon and data type
  , nested_stuff:title[0]::string as primary_title -- zero-based
from really_nested;

nested_got_dbplyr.R

# Snowflake seems to prefer ALL CAPS;
# if you run into errors try using them, and/or putting field names in quotes.
library(tidyverse)
library(rJava)             # connect to Java
library(dbplyr)            # database back end for the tidyverse's dplyr
library(dplyr.snowflakedb) # the snowflake connector

# I set these connection parameters in my .Renviron file;
# there are better ways to do it
# see https://github.com/snowflakedb/dplyr-snowflakedb for more on connecting to your DB
options(dplyr.jdbc.classpath = Sys.getenv("SNOWFLAKE_DRIVER"))

snowflake_connection <- 
  src_snowflakedb(
    user     = Sys.getenv("SNOWFLAKE_USER"),
    password = Sys.getenv("SNOWFLAKE_PWORD"),
    account  = Sys.getenv("SNOWFLAKE_ACCOUNT"),
    host     = Sys.getenv("SNOWFLAKE_URL"),
    opts     = list(
      warehouse = "GOT_WAREHOUSE",
      db        = "GOT_DB"
    )
  )

# here's where the magic happens;
# notice how sql() injects Snowflake SQL to query the JSON,
# analogous to tidyr::hoist()
snowflake_connection %>%
  tbl(in_schema("GOT_SCHEMA", "REALLY_NESTED")) %>% 
  mutate(
    name = sql('NESTED_STUFF:name::string'),
    primary_title = sql('NESTED_STUFF:title[0]::string')
  ) %>%
  rename_all(str_to_lower) # I prefer lowercase!