How to BigQuery json "struct" to rows?

Question

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!

Answer 1

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