title: Unnest stringified array of json objects in BigQuery
body: <p>I have a table that contains a <code>string</code> column containing a stringified list of JSON objects like so:</p>
  
  <pre><code>'[{"a": 5, "b": 6}, {"a": 7, "b": 8}]'
  </code></pre>
  
  <p>I would like to unnest this array, and then use <code>json_extract()</code> or <code>json_extract_scalar()</code> to get the values out of these objects.</p>
  
  <p>It's unclear from <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions" rel="nofollow noreferrer">BigQuery's JSON Function documentation</a> that I'm able to do so using baked-in functionality.</p>
  
  <p><strong>Is a UDF required to accomplish this, or does this functionality exist in BigQuery?</strong></p>
  
  <p>The below UDF accomplishes what I'm looking for:</p>
  
  <pre><code>CREATE TEMP FUNCTION
    JSON_EXTRACT_ARRAY(input STRING)
    RETURNS ARRAY&lt;STRING&gt;
    LANGUAGE js AS """  
  return JSON.parse(input).map(x =&gt; JSON.stringify(x));
  """;
  
  with
  
  raw as (
    select
      1 as id,
      '[{"a": 5, "b": 6}, {"a": 7, "b": 8}]' as body
  )
  
  select
    id,
    json_extract(entry, '$.a') as a,
    json_extract(entry, '$.b') as b
  from
    raw,
    unnest(json_extract_array(body)) as entry
  </code></pre>