Parse data in bigquery using split or regex

Question

I have this kind of data in platforminfo column

[{'df': {}, 'badKs': ['+81312277629'], 'objectId': 'JoF3NUHVYxDAoirH4e6i3g', 'platform': 'Web'}, {'df': {}, 'badKs': ['+81312277629'], 'objectId': '-vfa995e3b5d384b7dbfd8919bad17c28a', 'platform': 'iOS', 'os_version': '14.1', 'app_version': '3.16.2', 'make': 'Apple', 'model': 'iPhone12,3'}]

[{'df': {}, 'objectId': 'elQ8JEW2klEQFMZ0vXZDhw', 'platform': 'Web', 'phone': 5370002731}, {'df': {}, 'objectId': '__da533f19d1e040bea2b2b58fa6d2ecad', 'platform': 'Android', 'phone': 5370002731, 'os_version': '10', 'app_version': '3.6.4', 'make': 'Samsung', 'model': 'SM-A605G'}]

what I want is to parse this data into many column, but there is actually same definition but different naming badKs=phone

my expectation would be like this:

phone         platform  os_version  make 
+81312277629  iOS       14.1        Apple
5370002731    Android   10          Samsung

I already did with this query

split(REGEXP_REPLACE(platforminfo, r'([\{\}\]\'\"])', ''), 'phone:')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(platforminfo, ']')) - 1)] end

however this query is not good enough to parse the data, would be helpful if you can some how help me on this case. thx

Mikhail Berlyant · Accepted Answer · 2021-08-12 04:29:46Z

1

Use below approach

select 
  translate(coalesce(json_extract(el[offset(0)], '$.badKs'), json_extract(el[offset(0)], '$.phone')), "[]\"", "") as phone,
  json_extract_scalar(el[offset(1)], '$.platform') as platform,
  json_extract_scalar(el[offset(1)], '$.os_version') as os_version,
  json_extract_scalar(el[offset(1)], '$.make') as make
from `project.dataset.table`, unnest([struct(json_extract_array(platforminfo, '$') as el)])

if applied to sample data in your question - output is

answered Aug 12, 2021 at 4:29

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dede Soetopo Over a year ago

thx it worked! but somehow I'm trying to use the same method to extract the other column, but it doesn't work. is there any specific use for json_extract?

Collectives™ on Stack Overflow

Parse data in bigquery using split or regex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related