select

outputs[4]:value as raw_value

from aleo.core.fact_transitions

where program_id = 'arcn_pool_v2_2_2.aleo'

and function = 'create_pool'

and block_timestamp::date >= '2024-09-10'

and succeeded

),

cleaning as (

SELECT

-- Step 1: Add quotes around unquoted keys

REGEXP_REPLACE(raw_value, '(\\b\\w+):', '"\\1":') AS json_step1,

-- Step 2: Quote values with specific types and addresses

REGEXP_REPLACE(

REGEXP_REPLACE(

json_step1,

'(aleo1[a-z0-9]+)', -- Matches the "aleo1..." addresses

'"\\1"'

),

'([0-9]+(field|u128|u32|u16|u8))', -- Matches numbers ending in field, u128, u32, etc.

'"\\1"'

) AS json_step2,

json_step2,

':\\s*([a-zA-Z0-9_\\.]+)(\\s*[,\\]])', -- Match unquoted values after colons

': "\\1"\\2'

) AS cleaned_json,

-- Step 3: Remove any remaining unquoted keys if needed and finalize

try_parse_json(cleaned_json) AS final,

final:arguments[3]::string as pool_address

FROM raw

),