azure - 如何从数组中的嵌套对象中提取数据

Question

尝试通过 API 调用将一些数据加载到表中；问题是我似乎无法从数组内的嵌套对象中获取数据。

我已经成功加载了所有内容，减去了 Survey_data 的分割 - 所有这些都进入了一列。尝试将交叉应用与 openjson 结合使用，但未能提取选项和答案。

最终目标是[从选项对象]提取并加载 ID、选项、答案以及调查数据对象中的问题。

处理这些嵌套对象的好方法是什么？

有效负载示例(出于隐私考虑，删除了一些数据)

{
  "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
        }
      }
    }
  ]
}

提前致谢。

Answer 1

我不想这么说，但是随着动态对象名称的出现，需要使用动态工作的东西。

我的建议是使用高级数据操作连接器，更具体地说是Json to Table操作...

基本上，JSON 变量保存您在问题中提供的 JSON。

我使用Filter 选项仅提供answer 属性不等于null 的记录。

注意: json 中的最后一项看起来有些不对劲，所以只需检查一下即可。它可能会扭曲结果。

这产生了如下所示的结果输出......

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

现在，我不能 100% 确定这是否完全适合您，因为有许多属性未表示，因为该属性已被表示多次，但如果您只是在id、option 和 answer 属性，然后您就可以在那里看到它们。

显然，这是一个付费连接器，但它非常便宜并且可以满足您的需要。