Is there an elegant way to turn a BQ nested field in to a key:value JSON?

Viewed 168

I would love the option to turn the event_params nested BQ field into a JSON field?

My desired output should look like this: enter image description here

{"sessionId":123456789,"version":"1.005"}

2 Answers

Consider below

select *, (
  select '{' || string_agg(format('%s:%s',
    json_extract(kv, '$.key'),
    json_extract(kv, '$.string_value')
  )) || '}'
  from unnest(json_extract_array(to_json_string(event_params))) kv
) json
from `project.dataset.table`    

if applied to sample data in your question - output is

enter image description here

Update: I realized you changed/fixed data sample - so see updated query below

select *, (
  select '{' || string_agg(format('%s:%s',
    json_extract(kv, '$.key'),
    json_extract(kv, '$.value.string_value')
  )) || '}'
  from unnest(json_extract_array(to_json_string(event_params))) kv
) json
from `project.dataset.table`

with output

enter image description here

I made a version where you can define number fields in the JSON object with proper format, and you can filter for certain keys to end up in the JSON object:

with t as (
    -- fake example data with same format
    select * from unnest([
        struct([
            struct('session_id' as key, struct('123' as string_value) as value), 
                  ('timestamp', struct('1234567')), 
                  ('version', struct('2.23.65'))
                ] as event_params)
        ,struct([struct('session_id',struct('645')),('timestamp',struct('7653365')),('version',struct('3.675.34'))])
    ])
    
)

-- actual query
select 
   event_params,      -- original data for comparison
   format('{ %s }',   -- for each row create one json object:
    (select           -- string_agg will return one string with all key-value pairs comma-separated
         string_agg(  -- within aggregation create key-value pairs
             if(key in ('timestamp','session_id'),            -- if number fields 
                format('"%s" : %s',key,value.string_value),   -- then number format
                format('"%s" : "%s"',key,value.string_value)) -- else string format
             , ', ')
     from unnest(event_params)   -- unnest turns array into a little table per row, so we can run SQL on it
     where key in ('session_id','version')   -- filter for certain keys
    )   -- subquery end
   ) as json
from t

result table

Related