BigQuery JSON – Scalar subquery produced more than one element

I am trying to parse this json in BQ.

{
  "variations":[
    {
      "prime":{
        "name":[
          {
            "variant":{
              "indicator":["helm"]
            }
          },
          {
            "variant":{
              "indicator":["chart"]
            }
          }]
      }
    }]
}

My desired output is

variant
helm,chart

I have tried the query below and I get the error: Scalar subquery produced more than one element.. If I rewrite this to use a nested arrays, everything works perfect. however, when I try to use a scalar subquery by following the same concept, it doesn’t work. I think this should work but I don’t know what I am missing.

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

Any help will be appreciated.

Answer

Consider below approach

select 
  ( select string_agg(trim(indicator, '"')) 
    from unnest(json_extract_array(json_string, '$.variations')) variation,
    unnest(json_extract_array(variation, '$.prime.name')) variants,
    unnest(json_extract_array(variants, '$.variant.indicator')) indicator
  ) variant
from your_table               

if applied to sample data in your question – output is