Taking the latest date change - Json file without labels
25/02/2023 11:16
with src
as
(
select
try_parse_json(response) as response
from "RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
order by ingest_timestamp desc limit 1
)
,flatten_cols
as
(
select distinct
c.index as col_index,
c.value:name::string as col_name,
c.value:type::string as col_type
from src,
lateral flatten (input => src.response:tables) t,
lateral flatten (input => src.response:tables[t.index]:columns) c
order by 1
)
,flatten_rows
as
(
select
v.seq as row_index,
v.index as col_index,
v.value::string as col_value
from src,
lateral flatten (input => src.response:tables) t,
lateral flatten (input => src.response:tables[t.index]:rows) r,
lateral flatten (input => src.response:tables[t.index]:rows[r.index]) v
)
,col_row_join
as
(
select
r.row_index,
c.col_name,
r.col_value::string as col_value
from flatten_cols c
join flatten_rows r on c.col_index = r.col_index
)
--select * from flatten_cols;
//select * from col_row_join;
select max(col_value) as latest_timestamp from col_row_join where col_name = 'timestamp' ;with src
as
(
select
try_parse_json(response) as response
from "RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
order by ingest_timestamp desc limit 1
)
,flatten_cols
as
(
select distinct
c.index as col_index,
c.value:name::string as col_name,
c.value:type::string as col_type
from src,
lateral flatten (input => src.response:tables) t,
lateral flatten (input => src.response:tables[t.index]:columns) c
order by 1
)
,flatten_rows
as
(
select
v.seq as row_index,
v.index as col_index,
v.value::string as col_value
from src,
lateral flatten (input => src.response:tables) t,
lateral flatten (input => src.response:tables[t.index]:rows) r,
lateral flatten (input => src.response:tables[t.index]:rows[r.index]) v
)
,col_row_join
as
(
select
r.row_index,
c.col_name,
r.col_value::string as col_value
from flatten_cols c
join flatten_rows r on c.col_index = r.col_index
)
--select * from flatten_cols;
//select * from col_row_join;
select max(col_value) as latest_timestamp from col_row_join where col_name = 'timestamp' ;