I have a table with extracted currency exchange rates refreshing every day. It's schema is Date_field / JSON_field I need to "unnest" the json to be able to use it further with BigQuery sql as far as I believe.
The final json part that I can't trim down more looks like that: {"AED":3.6725,"AFN":94.0551,"ALL":107.9775,"AMD":480.2637,"ANG":1.79,"AOA":590.8707,....}
I have tried different json commands _query,_value,_extract etc. Tried turning it to string and replacing braces but it is not recognized as an array. I am probably missing something simple (at least I hope so) so I would appreciate the advice. Is that possible thorugh BigQuery sql alone? Thanks in advance for your time!
Consider below approach
select as struct trim(y[0], '"') as key, y[1] as value
from exchange_rates,
unnest(split(trim(json, '{}'))) as x,
unnest([struct(split(x, ':') as y)])
If applied to sample data in y our question
with exchange_rates as (
select '{"AED":3.6725,"AFN":94.0551,"ALL":107.9775,"AMD":480.2637,"ANG":1.79,"AOA":590.8707}' as json
)
output is