Tag: google-bigquery

I am having a bigquery table with many columns including one array type column of zero or many integers. For instance: Using BigQuery SQL, I want to generate another column exists if any value of tuple (3, 10) is included in some_numbers arrays. My desired output: How can I do this? Answer Consider below appr…

How to extract date from a string like ‘2:24 PM 6-20-2021’? For example, I have a column called Dates (datatype ‘String’) in the table like below – The output should be Answer How to extract date from a string? If you want to extract date as a date type so you will be able then t…

I currently have two queries that i’ve stitched together using views in Bigquery. I am wondering if there’s a way to combine them into a single query and eliminate the need for multiple views. The first query concatenates a few strings to create a field “id” The second query de-dupes t…

I have a table, where some texts are atrociously big. I want to make sure every row in the query output does not exceed, say, 100.000 characters. How do I do that? Here is a quick sample: Let’s say I want output text column to be less than 10 characters. So, I need this result: It would be even better

I have a table with 100k records. I want to create an array for each 10k items and loop this, i.e. first 10k items-> array(first 10k items), then seconds array(10k different items), then third and so on. In other words, each array contains 10k different records. This is what I tried: This is the error I ge…

I want to aggregate a pair of columns as a dictionary of key,value pairs to construct a STRUCT or a JSON STRING over multiple records. Currently my implementation leverages the STRING nature of JSON & STRING_AGG to build such a JSON value: Which results in the following: Is there a more readable approach?…