Skip to content

Tag: google-bigquery

Why is json_extract_scalar returning null

I have a fairly simple string-formatted json column in a BigQuery database I am trying to flatten. id relationships 1 {‘ownerObject’: {‘data’: None}, ‘investmentObject’: {‘data’: {‘type’: ‘assets’, ‘id’: ’40’}}, ‘securityObject’: {‘data’: None}, ‘segmentObject’: {‘data’: None}, ‘dataItemObject’: {‘data’: {‘type’: ‘dataItems’, ‘id’: ‘13161’}}, ‘scenarioObject’: {‘data’: {‘type’: ‘scenarios’, ‘id’: ’13’}}} Running: Gives me: id Invest_Type 1 null I would expect the column Invest_Type

Hierarchy Queries in BigQuery

I have a data from ControlM (scheduler) this way I saw many solutions where they have started from first node and descended to last node. But that is likely to fan-out with this data. What I am looking for a fan-in solution, where I can start with any end node and come towards first node In this case the answer

Sort COUNT(CASE WHEN) results

I am taking a database of statuses and creating the statuses as columns in order to count how many records from a network exist in each status. I’d love to sort the results based on the Partnered column DESC, but I can’t figure out how or where to do that?? Here’s my code: Here are my results so far. row

Getting a Monthly Date Range

How can you make a date range in a big query? A date range starts from 29th of the month and ends with 28th of the next month. It should be like this Date | Starting Date | Ending Date 03-13-2020 | 02-29-2020 | 03-28-2021 06-30-2020 | 06-29-2020 | 07-28-2021 01-01-2021 | 12-29-2020 | 01-28-2021 11-11-2021 | 10-28-2021 | 11-29-2021

BigQuery – Scalar subquery produced more than one element –

I have this query that helps me to find separate key words within strings (very useful with utm_campaign and utm_content): For example: if I have a umt_campaign like this: us_latam_mkt_google_black-friday_audiencie-custom_NNN-NNN_nnn_trafic_responsiv The query from above will help me to separate each word with a _ in between. So I’ll have a result like this: utm_campaign country product budget source campaign audience

Unnecessary blank row in SQL query

Aim was to write a SQL query to return ride details from a public database. While going through a data analytics course, I stumbled across this problem. the code used is correct as the instruction provided was the same however the result I obtained was different. The first row returned a blank row with just the number of trips mentioned.