title: Bigquery: Filter through JSON array
body: <p>I am having an issue filtering through an json array:</p>
  <pre><code>WITH table AS ( SELECT *
  FROM UNNEST([
    '{&quot;class&quot; : {&quot;students&quot; : [{&quot;type&quot; : &quot;a&quot;, &quot;name&quot;:&quot;David&quot;},{&quot;type&quot; : &quot;b&quot;, &quot;name&quot;:&quot;Max&quot;}]}}',
    '{&quot;class&quot; : {&quot;students&quot; : [{&quot;type&quot;: &quot;b&quot;, &quot;name&quot;:&quot;Jamie&quot;},{&quot;type&quot; : &quot;a&quot;, &quot;name&quot;:&quot;Martin&quot;}]}}',
    '{&quot;class&quot; : {&quot;students&quot; : [{&quot;type&quot; : &quot;a&quot;, &quot;name&quot;:&quot;Julie&quot;}, {type: &quot;b&quot;&quot;name&quot;: &quot;Jamie&quot;}]}}'
    ]) AS json_payload)
  </code></pre>
  <p>So having a json array I am trying to filter through and get all names that type ='a'. Each array can have just 1 type='a'</p>
  <p>My current solution is :</p>
  <pre><code>SELECT 
  CASE 
    WHEN JSON_VALUE(json_payload,'$.class.students[0].type') ='a'
      THEN JSON_VALUE(json_payload,'$.class.students[0].name') 
    WHEN JSON_VALUE(json_payload,'$.class.students[1].type') ='a'
      THEN JSON_VALUE(json_payload,'$.class.students[1].name') 
    END AS myName
  FROM table
  </code></pre>
  <p>I was thinking if there is a better/cleaner solution then this</p>
  <p>Thank you!</p>