CODEWITHSUNDEEP
google-bigquery
sam
sam

Reputation: 407

How to parse dictionary object where value is also a dictionary in bigquery table?

There is a column in my table that has this value

{"10037":{"id":"10550","name":"Original","index":0},"10403":{"id":"104470","name":"Original","index":0},"105852":{"id":"10608","name":"Success","index":1},"1052":{"id":"105993","name":"Promo","index":0}}

I am trying to get all the "name"

Upvotes: 2

Views: 6170

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 1 obj_id, '{"10037":{"id":"10550","name":"Original","index":0},"10403":{"id":"104470","name":"Original","index":0},"105852":{"id":"10608","name":"Success","index":1},"1052":{"id":"105993","name":"Promo","index":0}}' AS object
)
SELECT obj_id, name
FROM `project.dataset.your_table`, 
UNNEST(REGEXP_EXTRACT_ALL(object, r'"name":"(\w+)"')) name

with result as 

Row obj_id  name     
1   1       Original     
2   1       Original     
3   1       Success  
4   1       Promo

Upvotes: 4

Related Questions