LEFT JOIN WITH UNNEST ON BIGQUERY

Question

I have a bigquery table which i want to left outer join with public dataset bigquery-public-data.crypto_bitcoin.transactions. As transactions table has two nested fields inputs and outputs. The result of join also has nested values. I want to remove those.

SELECT *
FROM `asymmetric-glow-274808.mydataset.txid` as mytable
LEFT JOIN `bigquery-public-data.crypto_bitcoin.transactions` as datasource
ON mytable.string_field_3 = datasource.hash
LIMIT 1

Example

mytable
txid
1

transactions
hash    inputs.index  inputs.type outputs.index outputs.type
1       0              aaa            0          aaa
        1              bbb            1          bbb

After join im getting
txid hash    inputs.index  inputs.type outputs.index outputs.type
1      1           0              aaa            0          aaa
                   1              bbb            1          bbb

what i want
txid hash    inputs.index  inputs.type outputs.index outputs.type
1      1           0              aaa            0          aaa
1      1           1              bbb            1          bbb

how to do it?

rmesteves rmesteves · Accepted Answer · 2020-05-22T11:53:27

It seems you need something like

SELECT
  *
FROM
  mytable
LEFT JOIN (
  SELECT
    datasource.hash, inputs, outputs
  FROM
    `bigquery-public-data.crypto_bitcoin.transactions` AS datasource,
    datasource.inputs inputs,
    datasource.outputs outputs) d
ON
  mytable.string_field_3 = d.hash
LIMIT
  100

LEFT JOIN WITH UNNEST ON BIGQUERY

1 Answers