How can I unnest a string of an array of structs in bigquery?

Question

The bigquery method: INFORMATION_SCHEMA.TABLE_OPTIONS returns a string of arrays of structs for option_value when option_name is "labels". More information is here: TABLE_OPTIONS For example, I create the table:

CREATE OR REPLACE TABLE sample_dataset.sample_table
OPTIONS(
    labels=[("env","dev"),("dep","hr")]
)

I then query from the table:

SELECT * FROM sample_table.INFORMATION_SCHEMA.TABLE_OPTIONS

This returns the following:

[
  {
    "table_catalog": "sample_project",
    "table_schema": "sample_dataset",
    "table_name": "sample_data",
    "option_name": "labels",
    "option_type": "ARRAY<STRUCT<STRING, STRING>>",
    "option_value": "[STRUCT(\"env\", \"dev\"), STRUCT(\"dept\", \"hr\")]"
  }
]

How can I transform this table to something more like:

[
  {
    "table_catalog": "sample_project",
    "table_schema": "sample_dataset",
    "table_name": "sample_data",
    "env": "dev",
    "dept":"hr"

  }
]

I have tried all the answers to this stack overflow question with no luck: Stringified array bigquery

Answer 1

Consider below approach

select table_catalog, table_schema, table_name, 
  array(
    select as struct arr[offset(0)] key, arr[offset(1)] value
    from unnest(regexp_extract_all(option_value, r'STRUCT\(("[^"]+", "[^"]+")\)')) kv, 
    unnest([struct(split(replace(kv, '"', ''), ', ') as arr)]) 
  ) options 
from sample_dataset.INFORMATION_SCHEMA.TABLE_OPTIONS
where table_name = 'sample_data'
and option_name = 'labels'       

if applied to sample use case in your question - output is