CODEWITHSUNDEEP
jsongoogle-bigquery
Cristian Avendaño
Cristian Avendaño

Reputation: 477

Extract data from json in BigQuery

I have a column created_by in a BigQuery table, with text like this:

   created by      
  "{\"name\": \"Charles Darwin\", \"email\": \"[email protected]\"}"

The column is defined as a "STRING" type.

What I need is to extract the name and the email in two separated columns. How can I achieve this?

Thanks

Upvotes: 0

Views: 86

Answers (2)

Bihag Kashikar
Bihag Kashikar

Reputation: 1326

see below, link to google docs here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_value

select json_value(json "{\"name\": \"Charles Darwin\", \"email\": \"[email protected]\"}", '$.email') as getEmail

getemail

select json_value(json "{\"name\": \"Charles Darwin\", \"email\": \"[email protected]\"}", '$.name') as getName

getName

Upvotes: 1

Scott B
Scott B

Reputation: 3004

You may consider and try below approach using BigQuery's REGEX_EXTRACT function.

with sample_data as (
  select "{\"name\": \"Charles Darwin\", \"email\": \"[email protected]\"}" as my_string
)

select REGEXP_EXTRACT(my_string, r'"name":(\D+),') as ext_name,
REGEXP_EXTRACT(my_string, r'"email":(.+)}') as ext_email
from sample_data

Sample Output: enter image description here

My sample REGEX is the most basic one since I am not familiar with your complete data. You may enhance or create your own REGEX that can fit your needed validation based on your own analysis on your data.

Upvotes: 0

Related Questions