怎样使用SQL窗口函数对复杂Json数组进行展开与排名

unnest不能直接展开json数组，需先转为array类型；bigquery用json_extract_array()配合safe.unnest()，postgresql用jsonb_array_elements()；展开后须partition by业务主键以防排名错乱；避免重复json解析，注意null和空数组的兜底处理。

怎样使用sql窗口函数对复杂json数组进行展开与排名_结合unnest应用

UNNEST 不能直接展开 JSON 数组，必须先转换为 ARRAY 类型

BigQuery、PostgreSQL（12+）等支持 UNNEST 的引擎，**不接受原始 JSON 字符串作为输入**。如果你的字段是 JSON 类型（如 BigQuery 中的 JSON 列）或 STRING 类型存的 JSON 数组（如 '[{"id":1},{"id":2}]'），直接 UNNEST(my_json_col) 会报错：Function not found: UNNEST 或 Cannot unnest type JSON。

实操建议：

BigQuery：用 JSON_EXTRACT_ARRAY() 提取数组，再用 SAFE.PARSE_JSON() 或 JSON_QUERY() 转成可遍历结构，最后 UNNEST；若原字段是 STRING，先 PARSE_JSON() 再 JSON_EXTRACT_ARRAY()
PostgreSQL：用 jsonb_array_elements()（推荐）或 json_array_elements() 替代 UNNEST —— 它们专为 JSON 数组设计，返回一行一行的 jsonb/json 值
别硬套 UNNEST：很多用户卡在这一步，本质是混淆了「关系型数组」和「JSON 容器」——UNNEST 展开的是 SQL 数组（ARRAY<string></string>），不是 JSON 文本

展开后用 ROW_NUMBER() / RANK() 排名时，PARTITION BY 要对齐业务粒度

JSON 数组展开后，每条子记录默认“丢失”父级上下文。比如订单表里 items 是 JSON 数组，展开后需按 order_id 分组排序，否则 ROW_NUMBER() OVER (ORDER BY price DESC) 会对全量商品全局排名，而非“每个订单内最贵商品排第1”。

常见错误现象：

排名序号从 1 开始连续递增，但跨订单混排（没加 PARTITION BY order_id）
RANK() 出现重复序号却未按预期分组（如两个订单都出现 RANK = 1，但其实是各自独立的）
窗口函数写在 WHERE 后面，导致过滤后才排名 —— 正确顺序是：展开 → 窗口计算 → 过滤

示例（BigQuery）：

Browser Js

轻量级CDP浏览器控制，适用于AI代理。相较于内置浏览器工具，token消耗降低3‑10倍，仅在浏览时使用。

下载

SELECT
  order_id,
  item_name,
  price,
  ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price DESC) AS rn_in_order
FROM orders
CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(items, '$')) AS item_json
CROSS JOIN UNNEST([STRUCT(
  JSON_EXTRACT_SCALAR(item_json, '$.name') AS item_name,
  CAST(JSON_EXTRACT_SCALAR(item_json, '$.price') AS FLOAT64) AS price
)])

嵌套层级深时，避免多次 JSON_EXTRACT 导致性能陡增

每调一次 JSON_EXTRACT_SCALAR() 或 JSON_QUERY() 都触发一次 JSON 解析。如果数组有 100 项，每项提取 5 个字段，相当于做 500 次解析 —— 在 BigQuery 上可能让查询费用翻倍，在 PostgreSQL 上显著拖慢执行计划。

优化方向：

BigQuery：用 JSON_VALUE()（v2 函数，比 JSON_EXTRACT_SCALAR() 快 2–3 倍）替代旧函数；或提前用 JSON_EXTRACT_ARRAY() + TO_JSON_STRING() 缓存中间结构
PostgreSQL：用 jsonb_path_query() 一次性提取多层字段，例如 jsonb_path_query(items, '$[*] ? (@.price > 100).name')
关键原则：宁可在 UNNEST / jsonb_array_elements() 后用 CROSS JOIN UNNEST(ARRAY[...]) 构造结构化行，也不要反复解析同一段 JSON

NULL 和空数组处理不当会导致结果缺失或爆炸

UNNEST 和 jsonb_array_elements() 对 NULL 输入行为不同：前者跳过（静默丢弃），后者抛错或返回空集；空数组则两者都返回零行 —— 这意味着父记录彻底消失，而不是留一行 NULL 子项。

必须显式兜底：

BigQuery：用 COALESCE(JSON_EXTRACT_ARRAY(items), '[]') 确保非 NULL；再配合 SAFE.UNNEST() 防止空数组报错
PostgreSQL：用 COALESCE(items, '[]'::jsonb) + jsonb_array_elements(COALESCE(items, '[]'::jsonb))
如果业务要求“无 items 也要保留订单行”，就得改用 LEFT JOIN LATERAL jsonb_array_elements(...)（PostgreSQL）或 LEFT JOIN UNNEST(...) ON TRUE（BigQuery）

最容易被忽略的是：JSON 字段本身为 NULL 时，JSON_EXTRACT_ARRAY(NULL) 返回 NULL，而 UNNEST(NULL) 直接跳过整行 —— 这种静默丢弃很难被测试覆盖。