CODEWITHSUNDEEP
sqljsongoogle-bigquery
Diego Tribek
Diego Tribek

Reputation: 61

JSON EXTRACT IN BIG QUERY

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.

Upvotes: 1

Views: 176

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

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

Upvotes: 1

Related Questions