logo
down
shadow

Unable to un-nest some fields using google bigquery (standard)


Unable to un-nest some fields using google bigquery (standard)

By : Bhamore
Date : November 19 2020, 03:01 PM
I wish this helpful for you Expanding on Elliott's answer - I think here you First need to UNNEST, but then you most likely need to aggregate back your sku's. otherwise you will get quite redundant (flattened) output
I feel below is what you might need - it is for BigQuery Standard SQL
code :
#standardSQL
SELECT 
  player_id, 
  currency, 
  REV, 
  STRING_AGG(sku) SKUs
FROM (
  SELECT
    rev_info.user.id AS player_id,
    rev_info.purchase.total.currency AS currency,
    rev_info.purchase.total.amount AS REV,
    item.sku AS sku 
  FROM `gcs.rev` t,
  UNNEST(t.rev_info.purchase.virtual_items.items) item
)
WHERE currency = 'USD'
GROUP BY 1, 2, 3   
#standardSQL
SELECT
  rev_info.user.id AS player_id,
  rev_info.purchase.total.currency AS currency,
  rev_info.purchase.total.amount AS REV,
  (SELECT STRING_AGG(item.sku) 
     FROM UNNEST(t.rev_info.purchase.virtual_items.items) item
  ) AS SKUs 
FROM `gcs.rev` t,
WHERE currency = 'USD'


Share : facebook icon twitter icon
Nest multiple repeated fields in BigQuery

Nest multiple repeated fields in BigQuery


By : Niraame
Date : March 29 2020, 07:55 AM
I wish this help you With introduction of BigQuery Standard SQL we've got easy way to deal with records
Try below, Don't forget to uncheck Use Legacy SQL checkbox under Show Options
code :
WITH YourTable AS (
  SELECT 'a1' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u1' AS userid UNION ALL
  SELECT 'a1' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u2' AS userid UNION ALL
  SELECT 'a1' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u3' AS userid UNION ALL
  SELECT 'a1' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u4' AS userid UNION ALL
  SELECT 'a2' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u1' AS userid UNION ALL
  SELECT 'a2' AS item,  '2016-03-03 19:52:23 UTC' AS click_time, 'u2' AS userid
)
SELECT item, ARRAY_AGG(STRUCT(click_time, userid)) AS clicks
FROM YourTable
GROUP BY item
Nest function in Bigquery SQL standard

Nest function in Bigquery SQL standard


By : prabhjot lally
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Instead of NEST with +, you need to use ARRAY_AGG or STRING_AGG with CONCAT:
code :
ARRAY_AGG(CONCAT(trafficSource.source, '>', trafficSource.medium)) as source
STRING_AGG(CONCAT(trafficSource.source, '>', trafficSource.medium)) as source
how do i extract json from Google BigQuery using a function when the field contains @ sign BigQuery Standard SQL

how do i extract json from Google BigQuery using a function when the field contains @ sign BigQuery Standard SQL


By : face1st
Date : March 29 2020, 07:55 AM
This might help you In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets, [' ']. For example:
code :
SELECT 
 json_extract_scalar('{"@timestamp":"2018-08-08T09:21:57.947+00:00"}',"$['@timestamp']")
BigQuery: NEST alternative in Standard SQL

BigQuery: NEST alternative in Standard SQL


By : Clément
Date : March 29 2020, 07:55 AM
I hope this helps . Below is for BigQuery:
ARRAY_AGG() in Standard SQL is in fact equivalent of NEST() in Legacy SQL
code :
#legacySQL  
SELECT COUNT(1) rows FROM (
  SELECT NEST(x) y
  FROM (SELECT 1 AS x),
    (SELECT 2 AS x),
    (SELECT 3 AS x),
    (SELECT 4 AS x),
    (SELECT 5 AS x)
)
Row rows     
1   1    
#legacySQL  
SELECT NEST(x) y
FROM (SELECT 1 AS x),
  (SELECT 2 AS x),
  (SELECT 3 AS x),
  (SELECT 4 AS x),
  (SELECT 5 AS x)
Unable to convert this legacy SQL into Standard SQL in Google BigQuery

Unable to convert this legacy SQL into Standard SQL in Google BigQuery


By : Vivian Agura
Date : March 29 2020, 07:55 AM
hope this fix your issue I am not able to validate this legacy sql into standard bigquery sql as I don't know what else is required to change here(This query fails during validation if I choose standard SQL as big query dialect): , Most likely the error you are getting is something like below
code :
Duplicate column names in the result are not supported. Found duplicate(s): name   
SELECT
  lineitem.*,
  proposal_lineitem.*,
  porder.*,
  company.*,
  product.*,
  proposal.*,
  trafficker.name AS trafficker_name,
  salesperson.name AS salesperson_name,
  rate_card.*
FROM ( ...
#legacySQL
SELECT
  porder.*,
  trafficker.name,
  salesperson.name
FROM (
  SELECT 1 order_id, 'abc' order_name, 1 trafficker_id, 2 salesperson_id
) porder
LEFT JOIN (SELECT 1 id, 'trafficker' name) trafficker
  ON porder.trafficker_id =trafficker.id
LEFT JOIN (SELECT 2 id, 'salesperson' name ) salesperson
  ON porder. salesperson_id =salesperson.id
#standardSQL
SELECT
  porder.*,
  trafficker.name AS trafficker_name,
  salesperson.name AS salesperson_name
FROM (
  SELECT 1 order_id, 'abc' order_name, 1 trafficker_id, 2 salesperson_id
) porder
LEFT JOIN (SELECT 1 id, 'trafficker' name) trafficker
  ON porder.trafficker_id =trafficker.id
LEFT JOIN (SELECT 2 id, 'salesperson' name ) salesperson
  ON porder. salesperson_id =salesperson.id  
Related Posts Related Posts :
  • how to loop for division funciton in r
  • Why does ggplot not allow suppressing of messages generated by its geoms?
  • Download multiple excel files linked through urls in R
  • sparklyr : spark_apply function is not working in cluster mode
  • dplyr mutate - How do I pass one row as a function argument?
  • R selecting rows by conditions given in an external table
  • Native regex way to replace multiple leading chars with equal number spaces
  • stan - difficulty vectorizing
  • How to define a function that calls shiny functions?
  • How to count number of observations in a "n" dimensional range in R
  • Superimposing asymmetric t-distribution using ggplot2
  • Makefile to render all targets of all .Rmd files in directory
  • Authentication failure with rdrop2
  • DT data table display error
  • Issue when adding new rows (with nested dataframes within) to a dataframe
  • R-How to compare two dataframe and update list column value
  • Series vector for approximating pi
  • what is difference between "variance explained " in Random Forest and "merror" in XGBoost
  • R - Cast dataframe on unique rows - reshape2
  • ggplot2: plot correct proportions using geom_bar
  • Speedup query for R data.table - can this two-argument function be applied by group more quickly?
  • apply a function to several columns at once with mutate
  • R 'cowplot' neatly produce gridded plot with shared (common) legends and unique legends
  • Repeat R script for many times and save results to text file
  • How to negative lookbehind for special characters
  • data.table inner join produces error when no match is found
  • Create a new column base on existing column, but row above
  • Is there a way to visualize the process of source() in RStudio?
  • google places api consumes 10 request but I am doing only 1
  • Statistical mode of a categorical variable in R (using mlv)
  • Using for-loop to mutate a data.frame in r
  • Make plot with regression line for mixed model
  • Shortcut to select matces cases in R studio
  • vectoriced norm/matrix multiplication
  • Negative log10 transformation in R
  • Plot data with duplicate points
  • Visualizing crosstab tables with a plot in R - changing colours
  • How to manually modify automated numbers and labels in plot
  • How can I follow any redirections of a url in R?
  • Add jitter to box plot using markers in plotly
  • Adding an extra item to the legend
  • ggplot fills in data in the wrong order
  • Convert list to data frame
  • R: filtering by list(s) of strings and returning all results that start with the content of the lists
  • R:How to attach parts of a data frame with different headers and/or an overflowing piece of the dat frame
  • How to use 'par' for manipulating plot margins?
  • Can dplyr::case_when return mix of NAs and non-NAs?
  • Text preprocessing and topic modelling using text2vec package
  • Uploading multiple files in Shiny, process the files, rbind the results and return a download
  • R levelplot: color green-white-red (white on 0) according to one variable, but show the values of another variable
  • Why [i] doesn't point to the starting point in a vector
  • In R after generating a mvrnorm distribution, Y, what does Y[,1] do?
  • expand a data frame to have as many rows as range of two columns in original row
  • Getting started with R and CFA
  • Re order x-axis in ggplot so time goes from 12AM to 11PM in R
  • R - Automatically stack every nth column of a data frame and save them as new objects
  • How to format dplyr output in R into doubles (or other workable format)?
  • Dataframe to matrix conversion using tapply turns zeros to NAs
  • Smallest multiple of 1:20 - How can I make it quicker?
  • How to specify the size of a graph in ggplot2 independent of axis labels
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org