Query editor
WITH json_data AS (SELECT json_parse(http_get('https://api-data.asxn.xyz/api/hype-staking/metrics')) AS parsed_data),-- Extract daily summary metricsdaily_data AS (SELECT json_extract(parsed_data, '$.dailyData') AS daily_arrayFROM json_data),daily_summary AS (SELECTCAST(json_extract_scalar(item, '$.date') AS date) AS date,CAST(json_extract_scalar(item, '$.timestamp') AS bigint) AS timestamp,CAST(json_extract_scalar(item, '$.totalStake') AS double) AS total_stake,CAST(json_extract_scalar(item, '$.validatorCount') AS integer) AS validator_count,CAST(json_extract_scalar(item, '$.activeValidators') AS integer) AS active_validators,CAST(json_extract_scalar(item, '$.averageStakePerValidator') AS double) AS avg_stake_per_validator,CAST(json_extract_scalar(item, '$.yield') AS double) AS yield,CAST(json_extract_scalar(item, '$.stakingPercentage') AS double) AS staking_percentage,CAST(json_extract_scalar(item, '$.cumulativeStake') AS double) AS cumulative_stakeFROM daily_dataCROSS JOIN UNNEST(CAST(json_extract(daily_array, '$') AS array(json))) AS t(item)),-- Extract daily validator datavalidator_daily_data AS (SELECT json_extract(parsed_data, '$.validatorDailyData') AS validator_daily_arrayFROM json_data),unnested_validator_data AS (SELECTCAST(json_extract_scalar(day_item, '$.date') AS date) AS date,CAST(json_extract_scalar(day_item, '$.timestamp') AS bigint) AS timestamp,json_extract(day_item, '$.validatorStakes') AS validator_stakesFROM validator_daily_dataCROSS JOIN UNNEST(CAST(json_extract(validator_daily_array, '$') AS array(json))) AS t(day_item)),validator_stakes AS (SELECTdate,timestamp,CAST(json_extract_scalar(stake_item, '$.name') AS varchar) AS validator_name,CAST(json_extract_scalar(stake_item, '$.address') AS varchar) AS validator_address,CAST(json_extract_scalar(stake_item, '$.stake') AS double) AS stakeFROM unnested_validator_dataCROSS JOIN UNNEST(CAST(json_extract(validator_stakes, '$') AS array(json))) AS s(stake_item)),-- Extract current validator info for commission datacurrent_validators AS (SELECT json_extract(parsed_data, '$.currentValidatorStakes') AS current_validators_arrayFROM json_data),validator_info AS (SELECTCAST(json_extract_scalar(validator, '$.name') AS varchar) AS validator_name,CAST(json_extract_scalar(validator, '$.address') AS varchar) AS validator_address,CAST(json_extract_scalar(validator, '$.commission') AS double) AS commission,CAST(json_extract_scalar(validator, '$.isActive') AS boolean) AS is_activeFROM current_validators