Flatten json string into columns in big query-CodePudding

I am new to bigquery and trying to flatten the below JSON string into individual columns.

{"city": "", "country": "US", "email": "[email protected]", "province": "", "state": ""}

column_json,
json_extract_scalar(h, '$.city') as city
from 
table as l
left join unnest(json_extract_array(column_json)) as h

I tried the above code but all I am getting are nulls. How would I go about this? Any help will be greatly appreciated. thanks

CodePudding user response：

Use below approach

create temp function  get_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
  """;
create temp function  get_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
  """;
select * except (json) from (
  select json, key, value
  from your_table, 
  unnest(get_keys(json)) key with offset
  join unnest(get_values(json)) value with offset
  using(offset)
)
pivot (any_value(value) for key in ('city', 'country', 'email', 'province', 'state'))

if apply to sample data in your question - output is