BigQuery中存储键值数据：JSON与ARRAY<STRUCT>该如何选择？

针对你纠结的两种键值存储方式，结合你的使用场景（简单标量值、动态键），从几个核心维度对比分析：

1. 存储效率 #

• STRUCT数组：BigQuery对重复STRUCT类型有专门的存储优化，相同key的字符串会做字典编码，整体存储空间比JSON紧凑很多。比如重复出现的"env"、"region"这类键，只会存储一次字面量，其余行引用编码值，大幅节省空间。
• JSON列：本质是序列化的文本格式，额外的引号、逗号等符号会占用空间，且没有针对键值对的专属压缩优化，相同键值对的重复存储效率远不如STRUCT数组。

2. 查询性能 #

• STRUCT数组：无需解析文本，直接用数组原生函数操作，执行效率更高。比如筛选key为"env"且value为"prod"的行：

  WHERE EXISTS (
    SELECT 1 FROM UNNEST(labels) l 
    WHERE l.key = 'env' AND l.value = 'prod'
  )
  

  BigQuery的查询引擎对UNNEST、ARRAY_CONTAINS这类数组操作的优化非常成熟，大数据量下速度优势明显。
• JSON列：每次查询都需要先解析JSON文本，依赖JSON_EXTRACT_SCALAR等函数，会增加CPU开销。同样的筛选逻辑要写成：

  WHERE JSON_EXTRACT_SCALAR(json_col, '$.env') = 'prod'
  

  如果是动态未知键的场景，还需要先提取所有键再过滤，性能差距会进一步拉大。

3. 类型处理 #

• JSON列：确实支持原生标量类型（字符串、数字、布尔、null），查询时可以直接提取对应类型的值，比如JSON_EXTRACT(json_col, '$.count')能直接得到数值类型，无需转换。
• STRUCT数组：value固定为STRING类型，存储数字、布尔时需要先转成字符串，查询时再转回对应类型（比如CAST(l.value AS INT64)）。但你的场景是简单标量值，这种转换的成本极低，甚至可以通过封装UDF来简化操作。

4. 官方实践参考 #

Google在INFORMATION_SCHEMA.JOBS的labels列采用的是STRUCT数组方案，这说明在动态键值的场景下，STRUCT数组是经过验证的成熟选择。JSON类型虽然是较新特性，但在键值对的查询优化上，STRUCT数组的支持更完善。

最终推荐 #

• 优先选STRUCT数组：如果你的核心诉求是查询效率和存储成本，尤其是数据量较大的情况，它的性能和存储优势会非常明显，类型转换的额外成本在简单标量场景下可以忽略。
• 可选JSON列：如果你的场景中值类型多样，且频繁需要直接对原生类型做计算/逻辑判断，同时数据量不大，那么JSON列的便利性会更突出。

内容的提问来源于stack exchange，提问作者Cblair