Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Flatten BigQuery Table with JSON_EXTRACT_ARRAY - No matching signature for function JSON_EXTRACT_ARRAY

How can I best flatten a table with a JSON_EXTRACT_ARRAY query that flattens the below table?

I tried:

with raw as (
    select *
    from {{ source('hubspot','deal_pipelines') }} 
temp as (
select JSON_EXTRACT_ARRAY(stages,'$') as stages
from raw
select
    json_value(s,'$.label') as label
    ,json_value(s,'$.active') as active
from temp ,unnest(stages) as s
But I get this error:
No matching signature for function JSON_EXTRACT_ARRAY for argument types: ARRAY<STRUCT<value STRUCT<probability FLOAT64, displayorder INT64, active BOOL, ...>>>, STRING. Supported signatures: JSON_EXTRACT_ARRAY(STRING, [STRING]); JSON_EXTRACT_ARRAY(JSON, [STRING]) at [18:8]
Any suggestions how to fix it are greatly appreciated.
stages has a type of ARRAY<STRUCT<value STRUCT<probability FLOAT64, displayorder INT64, active BOOL, ...>>>, not a JSON string. So you seem to want below.
with raw as (
    select *
    from {{ source('hubspot','deal_pipelines') }} 
select
    s.value.label as label,
    s.value.active as active
from raw, unnest(stages) as s;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.