UNNEST不能直接展开JSON数组，需先转为ARRAY类型；BigQuery用JSON_EXTRACT_ARRAY()配合SAFE.UNNEST()，PostgreSQL用jsonb_array_elements()；展开后须PARTITION BY业务主键以防排名错乱；避免重复JSON解析，注意NULL和空数组的兜底处理。
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）：
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) 直接跳过整行 —— 这种静默丢弃很难被测试覆盖。
