在Snowflake中如何将JSON键值对列表转换为数组？

问题背景 #

你有如下JSON结构的订单数据：

{
  "Lines": {
    "1": "product a",
    "4": "product b"
  },
  "order_no": "9999"
}

目前通过parse_json(order_json):Lines:"1"这类语法按键访问单行产品，但希望将Lines的键值对转换为数组，从而可以用数组索引（如lines_array[0]）来访问元素。

解决方案 #

可以通过Snowflake的LATERAL FLATTEN展开JSON对象的键值对，再用ARRAY_AGG按键的数值排序生成有序数组，具体实现如下：

方法一：生成有序数组并支持索引访问

with orders as (
  select '{"order_no": "9999", "Lines": {"1": "product a", "2": "product b"}}' as order_json
  union all
  select '{"order_no": "8888", "Lines": {"1": "product a", "2": "product b", "3": "product c"}}'
  union all
  select '{"order_no": "7777", "Lines": {"4": "product b", "1": "product a"}}'
),
processed_orders as (
  select 
    parse_json(order_json):order_no::varchar as order_no,
    -- 按键的数值排序后聚合为数组
    ARRAY_AGG(lines_obj[key] ORDER BY key::int) as lines_array
  from orders,
  -- 展开Lines对象的所有键
  lateral flatten(input => OBJECT_KEYS(parse_json(order_json):Lines)) as keys,
  -- 关联原Lines对象
  lateral (select parse_json(order_json):Lines as lines_obj)
  group by order_no, lines_obj
)
select 
  order_no,
  lines_array,
  lines_array[0] as first_product, -- 访问数组第一个元素
  lines_array[1] as second_product -- 访问数组第二个元素
from processed_orders;

方法二：更简洁的展开聚合写法

直接通过flatten展开键值对，再聚合排序：

with orders as (
  select '{"order_no": "9999", "Lines": {"1": "product a", "2": "product b"}}' as order_json
  union all
  select '{"order_no": "8888", "Lines": {"1": "product a", "2": "product b", "3": "product c"}}'
  union all
  select '{"order_no": "7777", "Lines": {"4": "product b", "1": "product a"}}'
)
select 
  parse_json(order_json):order_no::varchar as order_no,
  -- 按键的数值排序生成数组
  ARRAY_AGG(value ORDER BY key::int) as lines_array
from orders,
-- 展开Lines对象的键值对（mode='keys'返回键值对）
lateral flatten(input => parse_json(order_json):Lines, mode => 'keys')
group by order_no;

关键说明 #

• LATERAL FLATTEN用于将JSON对象的键值对展开为行数据；
• ARRAY_AGG(...) ORDER BY key::int确保数组元素按原键的数值顺序排列（比如键1、4会对应数组索引0、1）；
• 生成数组后，即可通过[索引]语法直接访问对应位置的元素，索引从0开始。

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