BigQuery scripting: running a SQL query based on each row included in a table column

Question

My origin table includes url_id and SQL [standard sql, string]

I would like to run each of the SQL statements in the string column "SQL" and join the outcome with the correspondent url_id

expected outcome

UPDATE

to make this easy to reproduce I´ve imported a similar schema from public BQ:

bigquery-public-data.covid19_italy.data_by_province

if I run

select string_agg(
    format('select %i as url_id, (%s) as simpleSKU', url_id, sql), 
    ' union all '   )

FROM `xxx.staging.test_query

it returns

Mikhail Berlyant · Accepted Answer · 2021-01-26T03:33:08.133

2

Consider below

execute immediate (
  select 'select url_id, region_name from (' || string_agg(
    format('select %i as url_id, array(%s) as region_names', url_id, sql), 
    ' union all '
  ) || '), unnest(region_names) as region_name'
  from your_table
);

if applied to sample data in your question - output it

edited Jan 26 '21 at 03:33

answered Jan 25 '21 at 19:51

Mikhail Berlyant

165,386
8
154
230

hi it returns me Query error: Scalar subquery produced more than one element at [1:1] I have also updated the question to make it more debuggable – Shahin Ghannadian Jan 26 '21 at 03:01
i think i tested this and it worked as expected. will check one more time shortly. stay tuned :o) – Mikhail Berlyant Jan 26 '21 at 03:21
see update in my answer. looks like when i initially tested - i used oversimplified dummy data returning only one row per url_id thus the error for real life data - it is fixed now – Mikhail Berlyant Jan 26 '21 at 03:43
thank you, do you know if that´s possible to create a table or save somehow the execute immediate? – Shahin Ghannadian Jan 30 '21 at 22:16

BigQuery scripting: running a SQL query based on each row included in a table column

1 Answers1

Linked