Is it possible to count the number of times each key has occurred in a column of JSON?
<p>I have a BigQuery table with a single column which has JSON in it.</p>

<p>I would like to output the count of the number of times each key appears in the column, and then sort by counts in descending order. The values associated with all of the keys are <code>1</code>.</p>

<p>There is a known / finite number of keys per object, but I'd rather not rely on it in case the largest object seen changes.</p>

<p>There is a known / finite number of keys overall, but I'd like to not rely on enumerating / updating a list as it changes.</p>

<p>e.g. input: three rows, one column named "json"</p>

<pre><code>[
  {"json": "{'A': 1}"},
  {"json": "{'B': 1}"},
  {"json": "{'B': 1, 'C': 1}"}
]
</code></pre>

<p>e.g. output: three rows, two columns named "key" and "count"</p>

<pre><code>[
  {"key": "B", "count": 2},
  {"key": "A", "count": 1},
  {"key": "C", "count": 1}
]
</code></pre>

<p>What's the simplest way of doing this, given that I don't want to rely on the finite number of keys per object and overall?</p>