0
votes

I want to query a subset of a record in the bitcoin blockchain using the google bigquery database. I go here and click view dataset https://console.cloud.google.com/marketplace/details/bigquery-public-data/bitcoin-blockchain. Then, on the left sidebar, it seems you have to click the dropdown at 'bigquery-public-data', then click 'bitcoin_blockchain' then 'transactions'. Then on the right you have to click the button 'Query Table'. This is the only way I have found to select the table -- just copying and pasting the command below won't recreate the error.

Based on the table that appears following the above instructions, I noticed that outputs are arecord type. I would like to view only one string from inside the record. The string is called output_pubkey_base58.

So I read the docs, and the docs imply the command would be:

SELECT outputs.output_pubkey_base58 FROM `bigquery-public-data.bitcoin_blockchain.transactions` LIMIT 1000;

I get an error: Cannot access value on Array<Struct<output_satoshis ... .. I tried outputs[0].output_pubkey_base58, didn't work

The annoying thing is that this problem is in the same format as the first example, where they query the citiesLived.place parameter from inside the citiesLived record in the same kind of command. : https://cloud.google.com/bigquery/docs/legacy-nested-repeated

2

2 Answers

2
votes

You need to unnest the array into a new variable.

SELECT o.output_pubkey_base58
FROM
  `bigquery-public-data.bitcoin_blockchain.transactions`, 
UNNEST (outputs) as o 
LIMIT
  1000
0
votes

Feel the confusion here is about legacy SQL and standard SQL. UNNEST must be used in standard SQL as described in document: https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#differences_in_repeated_field_handling

Selecting nested repeated leaf fields Using legacy SQL, you can "dot" into a nested repeated field without needing to consider where the repetition occurs. In standard SQL, attempting to "dot" into a nested repeated field results in an error.