Trying to extract json value from one to many columns on bigquery

user_11613815 0
Viewed 148

I am trying to model a table where one of the columns contains:

[{"code": "SEEYOUSOON100", "amount": "250.00", "type": "percentage"}]

Any solution from [bigquery documentation] website returns as null.

google-bigquery text-extraction json
1 Answers

Consider below approach

select 
  json_extract_scalar(json, '$.code') as code,
  json_extract_scalar(json, '$.amount') as amount,
  json_extract_scalar(json, '$.type') as type
from your_table, 
unnest(json_extract_array(col)) json

if applied to sample data in your question - output is

enter image description here

You can play/test with above using below cte

with your_table as (
  select '[{"code": "SEEYOUSOON100", "amount": "250.00", "type": "percentage"}]' col
)
select 
  json_extract_scalar(json, '$.code') as code,
  json_extract_scalar(json, '$.amount') as amount,
  json_extract_scalar(json, '$.type') as type
from your_table, 
unnest(json_extract_array(col)) json
user_52219440
Related