I’m translating a BigQuery below to Snowflake.
<code>select event_date,user_pseudo_id,event_name, event_timestamp,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key1' ) AS key1,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key2' ) AS key2
FROM `bigquery.analytics.events_20240812`;
</code>
<code>select event_date,user_pseudo_id,event_name, event_timestamp,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key1' ) AS key1,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key2' ) AS key2
FROM `bigquery.analytics.events_20240812`;
</code>
select event_date,user_pseudo_id,event_name, event_timestamp,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key1' ) AS key1,
(SELECT COALESCE( value.string_value, CAST(value.int_value AS string), CAST(value.double_value AS string), CAST(value.float_value AS string) ) FROM UNNEST (event_params) WHERE KEY = 'key2' ) AS key2
FROM `bigquery.analytics.events_20240812`;
I expect the result as below. The way I’m doing in Snowflake is creating 2 separate tables for key1 and key2, then full join them together. However, I will have hundreds of keys in the future. It would difficult to accomplish the goal with hundreds of joins.
Thank you so much!
Below is what I got from BigQuery and would expect the same for Snowflake.
1
If your events look like:
<code>with events(event_date, user_pseudo_id, event_timestamp,event_params) as (
select $1, $2, $3, parse_json($4) from values
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":-1.234},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"[UUID]"}
]'),
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":4.567},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"ot-group-id-COOO4"}
]')
)
</code>
<code>with events(event_date, user_pseudo_id, event_timestamp,event_params) as (
select $1, $2, $3, parse_json($4) from values
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":-1.234},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"[UUID]"}
]'),
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":4.567},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"ot-group-id-COOO4"}
]')
)
</code>
with events(event_date, user_pseudo_id, event_timestamp,event_params) as (
select $1, $2, $3, parse_json($4) from values
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":-1.234},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"[UUID]"}
]'),
('2024-08-12'::date, '111.11', 123456, '[
{"key":"key0", "float_value":4.567},{"key":"key1", "string_value":"Prifilled"},{"key":"key2", "string_value":"ot-group-id-COOO4"}
]')
)
which is too say, a row of values, and some JSON values, then FLATTEN is the most related to UNNEST command:
<code>select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
</code>
<code>select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
</code>
select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
gives:
which grouping via the SEQ and using MAX and IFF to pivot the values like:
<code>select
event_date,
user_pseudo_id,
event_timestamp,
min(iff(k='key1', v, null)) as key1,
min(iff(k='key2', v, null)) as key2
from (
select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
)
group by 1,2,3,seq
</code>
<code>select
event_date,
user_pseudo_id,
event_timestamp,
min(iff(k='key1', v, null)) as key1,
min(iff(k='key2', v, null)) as key2
from (
select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
)
group by 1,2,3,seq
</code>
select
event_date,
user_pseudo_id,
event_timestamp,
min(iff(k='key1', v, null)) as key1,
min(iff(k='key2', v, null)) as key2
from (
select
e.event_date,
e.user_pseudo_id,
e.event_timestamp,
f.value:key as k,
COALESCE( f.value:string_value, f.value:int_value::text, f.value:double_value::text, f.value:float_value::text) as v,
f.seq
from events as e,
table(flatten(input=>e.event_params)) as f
where f.value:key in ('key1', 'key2')
)
group by 1,2,3,seq
gives:
which appears to match your impled data and desired result.
2
<code>with cte as (
select
event_date,
user_pseudo_id,
event_timestamp,
ep.value:key as event_key,
a.seq,
coalesce(
ep.value:value:string_value,
ep.value:value:int_value::string,
ep.value:value:double_value::string,
ep.value:value:float_value::string
)::string as event_value
from
b_cmg_guoz_db.temp.events_json_all_sample a,
LATERAL FLATTEN(input => event_params) ep
)
select
*
from
cte
pivot(
min(event_value) for event_key in (
'key1',
'key2'
)
)
; </code>
<code>with cte as (
select
event_date,
user_pseudo_id,
event_timestamp,
ep.value:key as event_key,
a.seq,
coalesce(
ep.value:value:string_value,
ep.value:value:int_value::string,
ep.value:value:double_value::string,
ep.value:value:float_value::string
)::string as event_value
from
b_cmg_guoz_db.temp.events_json_all_sample a,
LATERAL FLATTEN(input => event_params) ep
)
select
*
from
cte
pivot(
min(event_value) for event_key in (
'key1',
'key2'
)
)
; </code>
with cte as (
select
event_date,
user_pseudo_id,
event_timestamp,
ep.value:key as event_key,
a.seq,
coalesce(
ep.value:value:string_value,
ep.value:value:int_value::string,
ep.value:value:double_value::string,
ep.value:value:float_value::string
)::string as event_value
from
b_cmg_guoz_db.temp.events_json_all_sample a,
LATERAL FLATTEN(input => event_params) ep
)
select
*
from
cte
pivot(
min(event_value) for event_key in (
'key1',
'key2'
)
)
; 1