Google BigQuery SQL: Parse multi level JSON (list +json+list+json) to Columns

Question

I have Json string with two levels: list[json{list[json]}]:

UserID               Json_List
100     [{"application_charge":{"id":13409353813,"name":"Starter","api_client_id":2485321},"usage_charges":[{"id":48216805,"description":"Extras","price":"60.70"}]}]
200     [{"application_charge":{"id":13409353814,"name":"Starter","api_client_id":2485322},"usage_charges":[{"id":48216890,"description":"Extras","price":"80.79"}]}]

Need Output like table :

UserID  application_charge.id   name    api_client_id   usage_charges.id    description   price
100      13409353813          Starter    2485321        48216805           Extras         60.7
200      13409353814          Starter    2485322        48216890           Extras         80.79

I manage to pull out the first step "application_charge" but do not understand how to get to the next step "usage_charges"

select  
  UserID,
  json_extract_scalar(json, '$.application_charge.id')  as id,
  json_extract_scalar(json, '$.application_charge.name')  as name,
  json_extract_scalar(json, '$.application_charge.api_client_id')  as api_client_id
from `be-prod-data.retailers_billing_data_production.shopify_application_charges`,
unnest(json_extract_array( json_list, '$')) json

How can I extract data from the second stage ??

Sergey Geron · Accepted Answer · 2021-03-15 11:56:55Z

Just one more unnest(json_extract_array(...

with mytable as (
  select 100 as userid, '[{"application_charge":{"id":13409353813,"name":"Starter","api_client_id":2485321},"usage_charges":[{"id":48216805,"description":"Extras","price":"60.70"}]}]' as json_list union all
  select 200, '[{"application_charge":{"id":13409353814,"name":"Starter","api_client_id":2485322},"usage_charges":[{"id":48216890,"description":"Extras","price":"80.79"}]}]'
)
select  
  UserID,
  json_extract_scalar(json, '$.application_charge.id')  as id,
  json_extract_scalar(json, '$.application_charge.name')  as name,
  json_extract_scalar(json, '$.application_charge.api_client_id')  as api_client_id,
  json_extract_scalar(nested, '$.id')  as usage_charges_id,
from mytable,
unnest(json_extract_array( json_list, '$')) json, unnest(json_extract_array(json, '$.usage_charges')) as nested

Collectives™ on Stack Overflow

Google BigQuery SQL: Parse multi level JSON (list +json+list+json) to Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related