JSON EXTRACT IN BIG QUERY

user_8604711 0
Viewed 36

I Currently have a column in json format with multiple items. The struct is like the following:

phones": [{"phone": "11111111", "type": "CELLPHONE"}, {"phone": "222222222", "type":"CELLPHONE"}, {"phone": "99999999", "type": "CELLPHONE"}]

I tried: json_extract(Contacts,'$.phones.phone') as phone_number but it only extracts the first one.

JSON_EXTRACT_ARRAY(Contacts,'$.phones') as phone_contacts gives me an array, But Im getting error trying to unnest it.

Does anybody know any approach to solve this problems?

Thanks in Advance.

google-bigquery sql json
1 Answers

Consider below

select json_value(phone_contact, '$.phone') phone,
  json_value(phone_contact, '$.type') type
from your_table, 
unnest(json_extract_array(Contacts,'$.phones')) phone_contact

if applied to sample data in your question - output is

enter image description here

user_52219440
Related