如何提取数组内嵌套对象的数据？API数据导入表格技术咨询

我正尝试将API调用返回的数据加载至表格中，但遇到无法从数组内嵌套对象提取数据的问题。目前已完成大部分数据的加载，但survey_data中的所有数据均存入了单个列；我曾尝试使用CROSS APPLY结合OPENJSON来提取option和answer，但未能成功。

我的最终目标是提取Options对象中的id、option、answer，并与survey_data对象中的question关联后加载到表格中。请问有什么合适的方法可以处理这类嵌套对象？

脱敏后的Payload示例 #

{  
  "data": [    
    {      
      "id": "8",      
      "contact_id": "12345",      
      "status": "Incomplete",      
      "is_test_data": "1",      
      "data_quality": [],      
      "region": "111",      
      "survey_data": {        
        "3": {          
          "id": 3,          
          "type": "parent",          
          "question": "I think apples are the best",          
          "section_id": 4,          
          "options": {            
            "10004": {              
              "id": 10004,              
              "option": "Agree",              
              "answer": "Agree"            
            }          
          },          
          "shown": true        
        },        
        "6": {          
          "id": 6,          
          "type": "parent",          
          "question": "I think oranges are the best",          
          "section_id": 4,          
          "options": {            
            "10019": {              
              "id": 10019,              
              "option": "Agree",              
              "answer": "Agree"            
            }          
          },          
          "shown": true        
        },        
        "5": {          
          "id": 5,          
          "type": "parent",          
          "question": "fruit care about my health",          
          "section_id": 4,          
          "options": {            
            "10014": {              
              "id": 10014,              
              "option": "Agree",              
              "answer": "Agree"            
            }          
          },          
          "shown": true        
        },        
        "7": {          
          "id": 7,          
          "type": "parent",          
          "question": "fruit are healthy",          
          "section_id": 4,          
          "options": {            
            "10024": {              
              "id": 10024,              
              "option": "Agree",              
              "answer": "Agree"            
            }          
          },          
          "shown": true        
        },        
        "33": {          
          "id": 33,          
          "type": "parent",          
          "question": "fruit help me focus",          
          "section_id": 4,          
          "options": {            
            "10052": {              
              "id": 10052,              
              "option": "Agree",              
              "answer": "Agree"            
            }          
          },          
          "shown": true        
        },        
        "12": {          
          "id": 12,          
          "type": "ESSAY",          
          "question": "i hope to...",          
          "section_id": 4,          
          "shown": true        
        }      
      }    
    },    
    {      
      "id": "9",      
      "contact_id": "67890",      
      "status": "Complete",      
      "is_test_data": "1",      
      "data_quality": [],      
      "region": "456",      
      "survey_data": {        
        "3": {          
          "id": 3,          
          "type": "parent",          
          "question": "I think Apples are the best.",          
          "section_id": 4,          
          "options": {            
            "10003": {              
              "id": 10003,              
              "option": "Strongly agree",              
              "answer": "Strongly agree"            
            }          
          },          
          "shown": true        
        },        
        "6": {          
          "id": 6,          
          "type": "parent",          
          "question": "I think oranges are the best",          
          "section_id": 4,          
          "options": {            
            "10018": {              
              "id": 10018,              
              "option": "Strongly agree",              
              "answer": "Strongly agree"            
            }          
          },          
          "shown": true        
        },        
        "5": {          
          "id": 5,          
          "type": "parent",          
          "question": "fruit care about my health",          
          "section_id": 4,          
          "options": {            
            "10013": {              
              "id": 10013,              
              "option": "Strongly agree",              
              "answer": "Strongly agree"            
            }          
          },          
          "shown": true        
        },        
        "7": {          
          "id": 7,          
          "type": "parent",          
          "question": "fruit are healthy",          
          "section_id": 4,          
          "options": {            
            "10023": {              
              "id": 10023,              
              "option": "Strongly agree",              
              "answer": "Strongly agree"            
            }          
          },          
          "shown": true        
        },        
        "33": {          
          "id": 33,          
          "type": "parent",          
          "question": "fruit help me focus",          
          "section_id": 4,          
          "options": {            
            "10053": {              
              "id": 10053,              
              "option": "Strongly agree",              
              "answer": "Strongly agree"            
            }          
          },          
          "shown": true        
        },        
        "12": {          
          "id": 12,          
          "type": "ESSAY",          
          "question": "I hope to...",          
          "section_id": 4,          
          "answer": "eat all the fruit",          
          "shown": true        
        }      
      }    
  ]}

针对这种嵌套的JSON结构，可以通过多层OPENJSON结合CROSS APPLY逐层解析嵌套对象，最终关联出需要的字段。以下是具体的SQL实现：

DECLARE @json NVARCHAR(MAX) = N'-- 替换为你的API返回JSON数据 --';

SELECT
    -- 顶层响应数据
    d.id AS response_id,
    d.contact_id,
    d.status,
    d.region,
    -- 问卷问题信息
    q.id AS question_id,
    q.question,
    q.type AS question_type,
    -- 选项与答案信息
    o.id AS option_id,
    o.option_text,
    o.answer,
    -- 处理ESSAY类型的直接答案
    CASE WHEN q.type = 'ESSAY' THEN q.essay_answer ELSE NULL END AS essay_answer
FROM OPENJSON(@json, '$.data')
WITH (
    id NVARCHAR(50) '$.id',
    contact_id NVARCHAR(50) '$.contact_id',
    status NVARCHAR(50) '$.status',
    region NVARCHAR(50) '$.region',
    survey_data NVARCHAR(MAX) '$.survey_data' AS JSON -- 将survey_data标记为JSON类型以便后续解析
) d
-- 解析survey_data中的每个问题对象
CROSS APPLY OPENJSON(d.survey_data)
WITH (
    id INT '$.id',
    question NVARCHAR(MAX) '$.question',
    type NVARCHAR(50) '$.type',
    options NVARCHAR(MAX) '$.options' AS JSON,
    essay_answer NVARCHAR(MAX) '$.answer' -- 提取ESSAY类型的直接答案
) q
-- 解析每个问题下的options对象（用OUTER APPLY保留无选项的ESSAY记录）
OUTER APPLY OPENJSON(q.options)
WITH (
    id INT '$.id',
    option_text NVARCHAR(MAX) '$.option',
    answer NVARCHAR(MAX) '$.answer'
) o
-- 过滤无效记录（可选）
WHERE o.id IS NOT NULL OR q.essay_answer IS NOT NULL;

代码说明 #

1. 第一层解析：解析最外层data数组，提取顶层字段，并将survey_data标记为JSON类型，为嵌套解析做准备。
2. 第二层解析：通过CROSS APPLY解析survey_data中的每个问题，提取问题ID、内容、类型，同时提取ESSAY类型问题的直接答案，并将options标记为JSON类型。
3. 第三层解析：用OUTER APPLY解析每个问题的options对象，提取选项ID、内容和答案。使用OUTER APPLY而非CROSS APPLY，是为了保留没有选项的ESSAY类型问题记录。
4. 过滤条件：可选过滤掉既无选项也无ESSAY答案的无效记录。

适配说明 #

如果JSON数据存储在数据表的字段中，只需替换开头的变量声明，改为从表中读取：

FROM YourResponseTable t
CROSS APPLY OPENJSON(t.json_column, '$.data')
-- 后续逻辑保持不变

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