尝试从API调用将一些数据加载到表中;问题是我似乎无法从数组中的嵌套对象中获取数据。
我已经设法加载了所有的内容，减去survey_data的拆分-所有内容都进入了一列。尝试使用openjson交叉应用，但无法提取选项和答案。
最终目标是[从Options对象]提取并加载id、option、answer沿着调查数据对象中的问题。
处理这些嵌套对象的合适方法是什么？
有效负载示例（出于隐私考虑，取出了一些数据）
{
"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
}
}
}
]
}先谢谢你了。
1条答案
按热度按时间n6lpvg4x1#
我不想这么说，但随着动态对象名称的出现，需要使用动态工作的东西。
我的建议是使用
Advanced Data Operations连接器，更具体地说是Json to Table操作。基本上，
JSON变量保存您在问题中提供的JSON。我使用了
Filter选项，只给予answer属性不等于null的记录。***注意：*json中的最后一项看起来不太对劲，所以只需检查一下。它可能会扭曲结果。
它产生的结果输出如下所示…
现在，我不能100%确定这是否完全符合您的要求，因为有许多属性没有表示，因为该属性多次表示，但如果您只在
id，option和answer属性之后，那么您可以在那里看到它们。显然，这是一个付费连接器，但它的地狱便宜，做你需要的。