相关文章推荐
想发财的火腿肠  ·  sys.sp_cdc_cleanup_cha ...·  1 周前    · 
空虚的毛豆  ·  node redis - NestJs ...·  1 年前    · 
心软的芒果  ·  GitHub 上 9 ...·  1 年前    · 
烦恼的凉面  ·  How can I see what's ...·  1 年前    · 
Collectives™ on Stack Overflow

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

Learn more about Collectives

Teams

Q&A for work

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

Learn more about Teams

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

Ask Question

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.