title: SQL - Extract JSON from Array
body: <p>I'm writing some queries with Google BigQuery. I want to extract the JSON from an array. Somehow I can extract it. And @Mikhail Berlyant helped me <a href="https://stackoverflow.com/questions/53368435/bigquery-unnest-an-array-getting-dupliates/53368514#53368514">here</a>. But now the problem is in extracting JSON from the array without Duplicates. </p>
  
  <h2>Current Structure:</h2>
  
  <p><a href="https://i.stack.imgur.com/FfVyS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FfVyS.png" alt="enter image description here"></a></p>
  
  <h2>I what I tried:</h2>
  
  <pre><code>WITH
    cte AS (
    SELECT
      labels,
      cost
    FROM
      BILLING.gcp_billing_export_v1)
  SELECT
    la,
    cost
  FROM
    cte,
    UNNEST(labels) AS la
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/H8DuD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/H8DuD.png" alt="enter image description here"></a></p>
  
  <p>See the cost box, the COST value is repeated twice, because we have 2 KEY, VALUE pairs in the array. </p>
  
  <p>So while doing <code>sum(cost)</code> with the group by <code>la.key</code> then I'm getting the wrong value. </p>
  
  <h2>What Im looking for is,</h2>
  
  <p><a href="https://i.stack.imgur.com/vSYt3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vSYt3.png" alt="enter image description here"></a></p>
  
  <p>Can anyone help me with this? </p>