CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
Simon Holstein
Simon Holstein

Reputation: 153

Converting Legacy SQL Query to Standard SQL

I am looking to convert the below Legacy SQL Query to Standard SQL. The issue I am having is that I need to unnest two tables (labels and credits). How can I convert this query? Thanks!

I run into a "Scalar subquery produced more than one element" whenever I try to rewrite this query (see below).

Legacy SQL Query I am trying to rewrite:

SELECT
  service.description,
  sku.description,
  usage_start_time,
  usage_end_time,
  labels.key,
  labels.value,
  cost,
  usage.amount,
  project.name,
  credits.name,
  credits.amount
FROM
  flatten([gcp_billing_data.gcp_billing_export],
    credits)

What I have tried so far in Standard SQL:

SELECT
  service.description AS service,
  sku.description AS sku,
  usage_start_time,
  usage_end_time,
  l.key,
  l.value,
  cost,
  usage.amount AS usage,
  project.name AS project,
  c.name AS credit,
  c.amount
FROM
  `gcp_billing_data.gcp_billing_export`,
  UNNEST(labels) AS l,
  UNNEST(credits) AS c
Group by 1,2,3,4,5,7,8,9,10,11

This query runs, but the number of rows is significantly less than I would expect.

Upvotes: 1

Views: 356

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

quick and formal fix for your query in Standard SQL is something like to replace below 

(select l.value from unnest(labels) as l)

with 

(select string_agg(l.value) from unnest(labels) as l)

But it is still not exactly the same as what initial version of your Legacy SQL version of query is doing

Upvotes: 1

Related Questions