相关文章推荐
老实的橙子  ·  Day 11 : psycopg2 操作 ...·  1 周前    · 
紧张的香瓜  ·  odps ...·  1 周前    · 
捣蛋的手套  ·  ABAP内表(internal ...·  3 天前    · 
听话的显示器  ·  css ...·  1 年前    · 
闷骚的木耳  ·  FrameworkElement.Layou ...·  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.