Converting Legacy SQL Query to Standard SQL

Question

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.

To me personally - the original query you are using - looks not kosher at all - as it duplicates multiple times cost and credits across the labels - so total results will end up in x times larger than in real. So, if you can provide simplified example of input data and expected output - and explain what exactly you want to get - this will expedite the answer :o) — Mikhail Berlyant
... while quick and formal fix is something like to replace (select l.value from unnest(labels) as l) with (select string_agg(l.value) from unnest(labels) as l) — Mikhail Berlyant
and what about credits?? should it be cross join with labels? — Mikhail Berlyant
@MikhailBerlyant I am not quite sure. You can see in the legacy SQL all I need to do is select the labels. So I am not sure the nature of the join via flatten and ",". I assume that is a cross join? Thanks for your help in your previous comment. That query now runs. — Simon Holstein
ok. so, btw, that is why I asked you to post simplified example of input and output so we can help you to understand what/how it should work. meantime - if it works for you - I will post it as an answer :o) — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-04-23T22:58:41

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

Converting Legacy SQL Query to Standard SQL

1 Answers