CODEWITHSUNDEEP
sqlgoogle-bigquery
David542
David542

Reputation: 110093

json to struct in BigQuery

If I have the following json:

[
    {
      "Source": "Internet Movie Database",
      "Value": "7.8/10"
    },
    {
      "Source": "Rotten Tomatoes",
      "Value": "89%"
    },
    {
      "Source": "Metacritic",
      "Value": "75/100"
    }
]

Is there a way to get BQ to be able to interpolate this as a struct? i.e., from the below it would be:

/* relaxed 'tuple' syntax: */
[('Internet Movie Database', '7.8/10'), ...]

/* 'full' syntax: */
[
    STRUCT('Internet Movie Database' as Source, '7.8/10' as Value),
    STRUCT('Rotten Tomatoes' as Source, '89%' as Value),
    STRUCT('Metacritic' as Source, '75/100' as Value)
] as Reviews

Upvotes: 1

Views: 2273

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL

#standardSQL
select 
  array(
    select as struct
      json_extract_scalar(rec, '$.Source') as Source, 
      json_extract_scalar(rec, '$.Value') as Value 
    from t.arr as rec
  ) as Reviews
from `project.dataset.table`, 
unnest([struct(json_extract_array(json) as arr)]) t

If applied to sample data from your question - output is

enter image description here

You can test above using below CTE

with `project.dataset.table` as (
  select '''
  [
      {
        "Source": "Internet Movie Database",
        "Value": "7.8/10"
      },
      {
        "Source": "Rotten Tomatoes",
        "Value": "89%"
      },
      {
        "Source": "Metacritic",
        "Value": "75/100"
      }
  ]
  ''' json
)

Upvotes: 7

Related Questions