CODEWITHSUNDEEP
sqlgoogle-bigquery
Tank
Tank

Reputation: 521

Google Bigquery SQL UNNEST and STRING_AGG

I have tried the following query:

SELECT STRING_AGG(inputs.addresses) as stringAgg, 
FROM `bigquery-public-data.crypto_bitcoin.transactions`
JOIN UNNEST (inputs) AS inputs
GROUP BY Date(block_timestamp)

However, I get the following error:

No matching signature for aggregate function STRING_AGG for argument types: ARRAY. Supported signatures: STRING_AGG(STRING); STRING_AGG(STRING, STRING); STRING_AGG(BYTES); STRING_AGG(BYTES, BYTES) at [2:3] Learn More about BigQuery SQL Functions.

Unsure how to get around this and get the list of addresses that are present in output. Error makes sense given the type addresses is:
[outputs. addresses STRING REPEATED Addresses which own this output.

Upvotes: 1

Views: 4990

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

inputs.addresses is an ARRAY by itself, so you need UNNEST it too
For example as in below 

#standardSQL
SELECT 
  DATE(block_timestamp) AS block_date,
  STRING_AGG(address) AS stringAgg
FROM `bigquery-public-data.crypto_bitcoin.transactions`
JOIN UNNEST (inputs) AS input, UNNEST(input.addresses) address
GROUP BY DATE(block_timestamp)
LIMIT 10

Obviously, above assumes that you want to collect all addresses per day as it is in your original query

Upvotes: 2

Related Questions