How to convert string representation dictionary into struct in gcp bigquery?

Viewed 107

How can actually convert the below string into STRUCT

select '{\"ID\": \"A\", \"QualifierID\": \"XYZ\", \"text\": \"Origin\"}';

I want the above string formatted text to be Struct type in GCP Bigquery.

3 Answers

Using a JSON function,

WITH sample_data AS (
  SELECT '{\"ID\": \"A\", \"QualifierID\": \"XYZ\", \"text\": \"Origin\"}' json
)
SELECT STRUCT (
         JSON_VALUE(json, '$.ID') AS ID,
         JSON_VALUE(json, '$.QualifierID') AS QualifierID,
         JSON_VALUE(json, '$.text') AS text
       ) AS struct_col
  FROM sample_data;

Or with JSON type,

WITH sample_data AS (
  SELECT '{\"ID\": \"A\", \"QualifierID\": \"XYZ\", \"text\": \"Origin\"}' json
)
SELECT STRUCT (
         STRING(PARSE_JSON(json).ID) AS ID,
         STRING(PARSE_JSON(json).QualifierID) AS QualifierID,
         STRING(PARSE_JSON(json).text) AS text
       ) AS struct_col
  FROM sample_data;

you can get following result:

enter image description here

Consider yet another option

create temp function keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
"""; 
create temp function  values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
"""; 
select * except(row_id) from (
  select to_json_string(t) row_id, key, value 
  from your_table t, unnest(keys(json)) key with offset
  join unnest(values(json)) value with offset
  using(offset)
)
pivot (any_value(value) for key in ('ID', 'QualifierID', 'text'))    

if applied to sample data in your question - output is

enter image description here

Does something like this work for you?

with _cte as (
select JSON '{\"ID\": \"A\", \"QualifierID\": \"XYZ\", \"text\": \"Origin\"}' as tojson
)
select struct(JSON_EXTRACT(tojson, '$.ID') as ID, 
   JSON_EXTRACT(tojson, '$.QualifierID') as QualifierID,
   JSON_EXTRACT(tojson, '$.text') as text) as tostruct
from _cte
Related