如何在SQL中指定数组Unnest后的排序并关联结果

核心解决方案是利用UNNEST(... WITH ORDINALITY)获取数组元素的原始位置索引，通过索引关联基线与响应数据，确保顺序完全匹配。

修改思路 #

默认UNNEST展开数组时不保证元素顺序与原数组一致，WITH ORDINALITY会为每个数组元素附加一个原始位置序号（从1开始递增），通过这个序号作为关联条件，就能精准匹配对应位置的基线和响应项。

1. 带索引的基线查询 #

select 
  form_submit_id
  , b.form_baselines as baseline
  , b.item_index as item_index
from claim_warehouse.aggregate_intake_form_baselines 
left join unnest(form_baselines) WITH ORDINALITY as b(form_baselines, item_index)
where form_submit_id=1234 
  and key="target"

2. 带索引的响应查询 #

SELECT 
  form_id 
  , form_submit_id 
  , m.metadata.answer_description as response
  , m.item_index as item_index
FROM client_warehouse.client_response_forms forms
left join unnest(form_metrics) WITH ORDINALITY as m(metadata, item_index) 
where form_submit_id = 1234 and m.key = 'response'

3. 完整关联查询 #

将两个带索引的子查询通过form_submit_id和item_index关联，确保位置匹配：

select 
  base.form_submit_id
  , forms.client_login_acct_num
  , base.baseline
  , resp.response
from (
  -- 带索引的基线数据
  select 
    form_submit_id
    , b.form_baselines as baseline
    , b.item_index as item_index
  from claim_warehouse.aggregate_intake_form_baselines 
  left join unnest(form_baselines) WITH ORDINALITY as b(form_baselines, item_index)
  where key="target"
) base
left join client_warehouse.client_response_forms forms
  on base.form_submit_id = forms.form_submit_id
left join (
  -- 带索引的响应数据
  SELECT 
    form_submit_id 
    , m.metadata.answer_description as response
    , m.item_index as item_index
  FROM client_warehouse.client_response_forms forms
  left join unnest(form_metrics) WITH ORDINALITY as m(metadata, item_index) 
  where m.key = 'response'
) resp
  on base.form_submit_id = resp.form_submit_id
  and base.item_index = resp.item_index
order by base.form_submit_id, base.item_index

关键说明 #

• WITH ORDINALITY是PostgreSQL等支持数组操作的SQL方言的标准语法，不同数据库可能有等价实现（比如BigQuery用UNNEST(... OFFSET(i))获取索引）。
• 如果基线与响应数组长度不一致，LEFT JOIN会保留所有基线项，对应响应项为NULL；若需要严格匹配长度，可改用INNER JOIN。

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