BigQuery JSON - Scalar subquery produced more than one element

user_4351039 0
Viewed 90

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.

google-bigquery sql json
2 Answers

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

enter image description here

user_52219440 
with cte as (
  select
    (struct(json_extract_array(json_extract(variations, '$.prime.name'), '$.') as prime_name)).prime_name
  from teste.teste, unnest(json_extract_array(json_string, '$.variations')) as variations 
)
select string_agg(trim(indicator, '"'))
from cte, 
 unnest(cte.prime_name) as pn,
 unnest(json_extract_array(json_extract(pn, '$.variant.indicator'), '$.')) as indicator
user_109923420
Related