title: Split json data into separate row result in Bigquery
body: <p>I am writing a bigquery code to split a JSON data set into a more structured table.</p>
  
  <p>The JSON_data_set looks something like this</p>
  
  <pre><code>Row | createdon | result
  1   | 24022020  | {"searchResult": {"searchAccounts": [{"chainName": "xyxvjw", "address": {"name": "xyxvjw - ythji", "combined_city": "uptown", "combined_address": "1 downtown, uptown, 09728", "city": "uptown"}, "products": ["pin", "needle", "cloth"]}},{"chainName": "pwiewhds", "address": {"name": "pwiewhds - oujsus", "combined_city": "over the river", "combined_address": "100 under bridge, over the river, 19920", "city": "over the river"}, "products": ["tape", "stapler"]}}],"searchID": "3abci832832o0"}}
  2   | 25020202  | {"searchResult": {"searchAccounts": [{"chainName": "xyxvjw2029", "address": {"name": "xyxvjw2029 - ythji", "combined_city": "uptown", "combined_address": "1 downtown, uptown, 09728", "city": "uptown"}, "products": ["pin", "needle", "cloth"]}},{"chainName": "pwiewhds8972", "address": {"name": "pwiewhds8972 - oujsus", "combined_city": "over the river", "combined_address": "100 under bridge, over the river, 19920", "city": "over the river"}, "products": ["tape", "stapler"]}}],"searchID": "3abci832832o0"}}
  </code></pre>
  
  <p>There are many subsequent account details in each row in the result column.
  Able to unnest the data using the below code to get column data such as chain name &amp; address. However, when I try to call the broken down field columns, it gives me the error <strong>Cannot access field _field_1 on a value with type ARRAY-STRUCT-STRING, STRING>></strong></p>
  
  <p>How can I separate the columns created from json data into individual columns and rows without being tied to the json row column?</p>
  
  <pre><code>CREATE TEMP FUNCTION json2array(json STRING)
  RETURNS ARRAY&lt;STRING&gt;
  LANGUAGE js AS """
     if (json !== null) {
      return JSON.parse(json).map(x=&gt;JSON.stringify(x));
     }
  """; 
  
  SELECT * EXCEPT(chains),
    ARRAY(SELECT AS STRUCT JSON_EXTRACT_SCALAR(x, '$.chainName'), JSON_EXTRACT_SCALAR(x, '$.address.combined_address') FROM UNNEST(chains) x WHERE JSON_EXTRACT_SCALAR(x, '$.chainName') IS NOT NULL) chain_names
  FROM (
    SELECT *,
       json2array(
        JSON_EXTRACT(result, '$.searchResult.searchAccounts')
       ) chains
    FROM json_data_set
  )
  </code></pre>