I would love the option to turn the event_params nested BQ field into a JSON field?
My desired output should look like this:

{"sessionId":123456789,"version":"1.005"}
I would love the option to turn the event_params nested BQ field into a JSON field?
My desired output should look like this:

{"sessionId":123456789,"version":"1.005"}
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
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
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