如何在BigQuery中调整表列存储字典值的键排列顺序

首先说明：JSON规范本身不定义键的顺序，仅当你的业务场景要求固定输出键顺序时才需要做如下处理。

方案1：待处理列是JSON类型 #

使用JSON_QUERY_ARRAY拆分数组，按指定顺序构造新JSON对象后重新聚合为数组，示例代码如下：

SELECT
  ARRAY_AGG(
    JSON_OBJECT(
      'start_day', JSON_VALUE(item.start_day),
      'end_day', JSON_VALUE(item.end_day),
      'start_time', JSON_VALUE(item.start_time),
      'end_time', JSON_VALUE(item.end_time)
    )
    ORDER BY OFFSET
  ) AS reordered_time_ranges
FROM your_table,
UNNEST(JSON_QUERY_ARRAY(time_ranges)) AS item WITH OFFSET
-- 若表有唯一主键，替换为按主键分组，性能更高
GROUP BY FORMAT('%t', your_table)

方案2：待处理列是字符串类型的JSON串 #

先将字符串解析为JSON类型，处理完成后再转回字符串即可，示例代码如下：

SELECT
  TO_JSON_STRING(
    ARRAY_AGG(
      JSON_OBJECT(
        'start_day', JSON_VALUE(item.start_day),
        'end_day', JSON_VALUE(item.end_day),
        'start_time', JSON_VALUE(item.start_time),
        'end_time', JSON_VALUE(item.end_time)
      )
      ORDER BY OFFSET
    )
  ) AS reordered_time_ranges_str
FROM your_table,
UNNEST(JSON_QUERY_ARRAY(PARSE_JSON(time_ranges_str))) AS item WITH OFFSET
-- 若表有唯一主键，替换为按主键分组，性能更高
GROUP BY FORMAT('%t', your_table)

注意事项 #

• JSON_OBJECT构造时传入键的顺序就是最终输出的键顺序，完全匹配你需要的start_day->end_day->start_time->end_time的顺序要求
• 代码中带上WITH OFFSET且聚合时按OFFSET排序，是为了保证数组内对象的原有顺序不会被打乱
• 请将代码中的your_table、time_ranges（JSON列名）、time_ranges_str（字符串JSON列名）替换为你实际的表名和字段名

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