我试图在BQ中解析这个json.
{
"variations":[
{
"prime":{
"name":[
{
"variant":{
"indicator":["helm"]
}
},
{
"variant":{
"indicator":["chart"]
}
}]
}
}]
}
我想要的输出是
variant |
---|
helm,chart |
我try 了下面的查询,得到了error: Scalar subquery produced more than one element.
分.如果我重写它,使用嵌套数组,一切都会很完美.然而,当我试图通过遵循相同的概念来使用标量子查询时,它不起作用.我认为这应该行得通,但我不知道我错过了什么.
SELECT
ARRAY(
SELECT
STRUCT(
STRING_AGG(
(SELECT
STRING_AGG(JSON_EXTRACT_SCALAR(z, '$'))
FROM UNNEST(
(SELECT
JSON_EXTRACT_ARRAY(y, '$.variant.indicator')
FROM UNNEST(JSON_EXTRACT_ARRAY(x, '$.prime.name')
) AS y
)
) AS z)
) AS variant
)
FROM UNNEST(JSON_EXTRACT_ARRAY(json_string, '$.variations')) AS x
) AS variations
FROM json_string
任何帮助都将不胜感激.