在Presto中从嵌套字典提取子问题并关联答案的实现方案

场景1：子问题与答案为同长度的独立数组 #

如果你的JSON结构是{"subquestions": ["子问题1", "子问题2"], "answers": ["答案1", "答案2"]}这种按位置关联的数组，可通过保留数组元素索引实现精准关联：

SELECT 
    q.qn_id,
    q.category,
    subq.subquestion,
    ans.answer AS Answer
FROM question_data q
-- 拆分子问题数组并保留位置索引
LEFT JOIN UNNEST(json_extract(q.content, '$.subquestions')) WITH ORDINALITY AS subq(subquestion, idx) 
    ON true
-- 通过索引关联对应位置的答案
LEFT JOIN UNNEST(json_extract(q.content, '$.answers')) WITH ORDINALITY AS ans(answer, idx) 
    ON subq.idx = ans.idx

注意事项： #

• 若JSON字段是字符串类型，需先用json_parse(q.content)替换q.content完成类型转换
• 用LEFT JOIN替代CROSS JOIN，避免丢失无subquestions的主表数据行

场景2：子问题与答案为嵌套键值对数组 #

如果你的JSON结构是{"subquestions": [{"q": "子问题1", "a": "答案1"}, {"q": "子问题2", "a": "答案2"}]}这种单数组嵌套键值对的形式，SQL实现更简洁：

SELECT 
    qn_id,
    category,
    json_extract_scalar(subq_item, '$.q') AS subquestion,
    json_extract_scalar(subq_item, '$.a') AS Answer
FROM question_data
LEFT JOIN UNNEST(json_extract(content, '$.subquestions')) AS subq(subq_item) 
    ON true

注意事项： #

• 直接拆分subquestions数组为单个JSON对象，再用json_extract_scalar提取目标字段
• 若JSON字段为字符串类型，需提前用json_parse(content)转换为JSON类型

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