title: Create a JSON column in BigQuery with the actual column values as keys
body: <p><strong>Is there a way to create a JSON in BigQuery with the column values as the keys?</strong></p>
  
  <p>I have 3 columns in the table:</p>
  
  <pre><code>user_id (string) | category (string) | info (struct)
  
  user_1, cat_A, info_1A
  user_1, cat_B, info_1B
  user_1, cat_C, info_1C
  user_2, cat_A, info_2A
  user_3, cat_Z, info_3Z
  user_3, cat_B, info_3B
  
  To abbreviate the values of the "info" column,
  let's say that it is a struct of i.e. {'f': 2, 'c': 3, ...}
  
  </code></pre>
  
  <p>I wanted to have this output, where the <em>keys</em> of the "features" column are the actual values of the "category" column:</p>
  
  <pre><code>user_id (string) | features (struct/JSON)
  user_1, {cat_A: info_1A, cat_B: info_1B, cat_C: info_1C, ...}
  user_2, {cat_A: info_2A}
  user_3, {cat_Z: info_3Z, cat_B: info_3B}
  </code></pre>
  
  <p>But instead, I'm currently only able to achieve this format (I made the output JSON-formatted for more clarity), where the <em>keys</em> are the pre-defined name you give when creating the STRUCT with i.e. <code>STRUCT(...) AS *key*</code>:</p>
  
  <pre><code>[
    {
      "user_id": "user_1",
      "features": [
        {
          "category": "cat_A",
          "features": {
            "f": 2,
            "c": 3,
          }
        },
        {
          "category": "cat_B",
          "features": {
            "x": 7,
            "z": 10,
          }
        },
        ...
    }
    ...
  ]
  </code></pre>
  
  <p>By using the query below:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
    user_id,
    ARRAY_AGG(
      STRUCT(
        category,
        STRUCT(f, c, x, z) AS features -- the different features for each category
      )
    )
  FROM ...
  GROUP BY user_id
  </code></pre>