Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Google BigQuery Unnesting Arrays

Does Matillion support unnesting Arrays in the GBQ component?

select
id
,array_item
from
dataset.table as a
,unnest(a.array) as array_item

6 Community Answers

Matillion Agent  

Paul Johnson —

Hi Joshua,

You should be able to leverage all of the Google Big Query functions and operators such as unnest by using the advanced mode on the Big Query Component and entering your query in the “SQL Query” property

Regards,
Paul


Joshua Vogel —

Advanced mode / Standard SQL, unfortunately the above results in:

400:Syntax error: Expected keyword JOIN but got ")" at [1:100]


Matillion Agent  

Kalyan Arangam —

hi Joshua,

Does your SQL work directly in bigquery console?

I assume you are using Standard mode. Is that explicitly selected in the component?

Previously, the component would default to Legacy mode but recent versions default to Standard. Just something to check.

Please share your SQL with us for review.

Best
Kalyan


Joshua Vogel —

Hello Kalyan,

The SQL works directly in the bigquery console. Here is the closest I can come up with, but this creates a different error when run in Matillion. This also works in console.

WITH sequences AS
(SELECT 'test1' AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 'test2' AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 'test3' AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers

- Josh


Matillion Agent  

Kalyan Arangam —

Hi Joshua,

Thanks for the SQL. I managed to replicate this issue.

Looks like this is due to some validations on matillion which do not seem to consider arrays as valid elements. I have raised a bug ticket for the same (EMD-6589).

In the interim, you should be able to bypass our validation checks by setting a connection parameter as shown below.

Connection Option – Other
Value – QueryPassthrough=true

Hope that helps.

Best
Kalyan


Joshua Vogel —

Awesome, this worked perfectly.

Post Your Community Answer

To add an answer please login