我怎样才能最好地使用JSON_EXTRACT_ARRAY查询来展开下面的表？
我试过：
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但我得到这个错误：
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]任何如何修复它的建议都将不胜感激。
1条答案
按热度按时间0vvn1miw1#
stages的类型是ARRAY<STRUCT<value STRUCT<probability FLOAT64, displayorder INT64, active BOOL, ...>>>，而不是 JSON 字符串。