是否可以计算每个键在JSON列中发生的次数？ [英] Is it possible to count the number of times each key has occurred in a column of JSON?

问题描述

我想输出每个键出现在每个键的次数列，然后按降序排列计数。与所有键相关的值是 1 。

每个对象有一个已知/有限数量的键，但我宁愿不要依赖它，以防万一所见最大的对象发生变化。

总体上有一个已知/有限数量的键，但是我想不会依赖枚举/更新列表。

例如输入：三行，一列名为json

  [
 {json：{'A' ：1}}，
 {json：{'B'：1}}，
 {json：{'B'：1，'C'：1} } 
] 
  

例如输出：三行，两列名为key和count。

  [
 {key： B，count：2}，
 {key：A，count：1}，
 {key：C，count：1} 
 
 $ / code> 

这样做最简单的方法是什么，考虑到我不想依赖在每个对象的有限数目的键和整体上？如果您禁用旧版SQL，您可以使用新的bigquery REGEX_EXTRACT_ALL函数，它看起来正是您要查找的内容： https://cloud.google.com/bigquery/sql-reference/functions-and-operators#regexp_extract_all

I have a BigQuery table with a single column which has JSON in it.

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 1.

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.

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

e.g. input: three rows, one column named "json"

[
  {"json": "{'A': 1}"},
  {"json": "{'B': 1}"},
  {"json": "{'B': 1, 'C': 1}"}
]

e.g. output: three rows, two columns named "key" and "count"

[
  {"key": "B", "count": 2},
  {"key": "A", "count": 1},
  {"key": "C", "count": 1}
]

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?

解决方案

If you disable Legacy SQL, you can make use of the new bigquery REGEX_EXTRACT_ALL function, which appears to be exactly what you're looking for: https://cloud.google.com/bigquery/sql-reference/functions-and-operators#regexp_extract_all

这篇关于是否可以计算每个键在JSON列中发生的次数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！