bq_unnest

Unnest an array column and cross joins it to the table

bq_unnest(input_tbl, select_columns, array_column, unnested_columns)

Arguments

input_tbl	A tbl object
select_columns	Normal columns to select
array_column	Array column to unnest
unnested_columns	Columns contained in the array that must be selected

Value

A tbl

Examples

if (FALSE) {
# A classic example from the vessel info tables in GFW
# The following corresponds to:
# <SQL>
# SELECT ssvid, un.value, un.count
# FROM
# `vi_ssvid_v20201209`
# CROSS JOIN
# UNNEST(ais_identity.n_imo) AS un

# Define a connection
con <- connection_open(
  bigquery(),
  project = "world-fishing-827",
  dataset = "gfw_research",
  billing = "emlab-gcp",
  use_legacy_sql = FALSE,
  allowLargeResults = TRUE)

# Define a tbl
vessel_info <- tbl(con, "vi_ssvid_v20201209")

# Unnest the tbl
vessel_info_unnested <-
  bq_unnest(input_tbl = vessel_info,                  # Define tbl
            select_columns = "ssvid",                 # Columns to select
            array_column = "ais_identity.n_imo",      # Array column
            unnested_columns = c("value", "count"))   # Columns to select once unnested

# Inspect the SQL code
dbplyr::show_query(vessel_info_unnested)

# Get the top 10 elements
head(vessel_info_unnested, 10)
}