Description Usage Arguments Value Examples
Unnest an array column and cross joins it to the table
1 | bq_unnest(input_tbl, select_columns, array_column, unnested_columns)
|
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 |
A tbl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | ## Not run:
# 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)
## End(Not run)
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.