0

Is there a function in BigQuery to create a typed STRUCT from a JSON object? Here is an example of what I'm trying to do:

with tbl as (
  SELECT
    STRUCT<name STRING, age INT64>("Bob", 20) AS person_as_struct,
    JSON '{"name": "Bob", "age": 20}' AS person_as_json
)
select
  -- STRUCT -> JSON
  TO_JSON(person_as_struct).age

  -- JSON -> STRUCT
  -- ???
  
from tbl
David542
  • 104,438
  • 178
  • 489
  • 842

1 Answers1

0

For your requirement, json_extract_scalar can be used which extracts scalar value and returns it as string. You can try the below sample query.

Query:

WITH
  jack AS (
  SELECT
    '''
  [
      {
        "Source": "Internet Movie Database",
        "Value": "7.8/10"
      },
      {
        "Source": "Rotten Tomatoes",
        "Value": "89%"
      },
      {
        "Source": "Metacritic",
        "Value": "75/100"
      }
  ]
  ''' json )
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
  jack,
  UNNEST([STRUCT(JSON_EXTRACT_ARRAY(json) AS arr)]) t

Output:

enter image description here

Shipra Sarkar
  • 1,385
  • 3
  • 10
  • Sue, but this is the same as the other comment -- it requires hardcoding the field names into the query and cannot be generalized, right? – David542 Dec 22 '22 at 20:44
  • Hi @David542, Since there is no direct function available, you can try the solution provided in this [StackOverflow Thread](https://stackoverflow.com/questions/68709560/bigquery-transform-generic-json-to-struct). Let me know if that resolves your issue. – Shipra Sarkar Dec 26 '22 at 13:50