arrays - 将 Rest API 嵌套 JSON 响应数据复制到 SQL
问题描述
我正在尝试从响应为嵌套 json 的 API 获取数据，我使用集合引用来捕获所有结果，但结果具有数组，其中默认情况下仅捕获第一个值。我还检查了文档（https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping），其中提到仅支持单个数组手术。请让我知道如何捕获所有嵌套数组 json 值。我也尝试使用 Map complex values to string 选项，但它不起作用。
请检查图像链接以获取 json 结果和 ADF 映射完成ADF 映射 JSON 响应
{
"story_count": 2325,
"next": "?page=2",
"previous": null,
"results": [
{
"id": 20111247056317,
"title": "ANZ exec bonuses cut, salaries hiked",
"summary": "[Source: AFR Online] ANZ chief executive Shayne Elliott and his executive team have had short-term bonuses cut by 50 per cent or more after profits slumped 42 per cent. However, they have almost all had sizeable increases in fixed pay that should stop them from harbouring any ill feelings towards their Melbourne-based employer. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=AFNROL0020201109egb9000p1",
"source_name": "Factiva",
"pub_date": "2020-11-09T06:52:18Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 15178,
"name": "Tax Risk",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 24194,
"name": "Rewards & benefits",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 17984,
"name": "AUSTRALIA AND NEW ZEALAND BANKING GROUP LIMITED",
"logo": "//112233.contify.com/client_data/custom_tag/logo/eg1lrjgo_400x400-17984.jpg"
}
],
"Duns Number": [
{
"id": 18498,
"name": "753682830",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [],
"duplicate_count": 0
},
{
"id": 20102741904140,
"title": "HSBC to cut up to 300 jobs in UK commercial banking unit",
"summary": "[Source: FinTech Futures News] HSBC has launched a restructuring of its commercial banking business in Britain. A source familiar with the matter tells Reuters that the plan will result in around 300 job losses. The cuts are part of a wider restructuring announced in February. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=FTEFN00020201027egar0002w",
"source_name": "Factiva",
"pub_date": "2020-10-27T00:00:00Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 24196,
"name": "Restructuring",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 17967,
"name": "HSBC Holdings PLC",
"logo": "//112233.contify.com/client_data/custom_tag/logo/nbzwhpqp_400x400-17967.png"
},
{
"id": 35194,
"name": "HSBC Holdings",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Duns Number": [
{
"id": 18655,
"name": "288451024",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [],
"duplicate_count": 0
},
{
"id": 20102641537340,
"title": "AT&T cuts more than 8,700 workers in third quarter amid COVID-19",
"summary": "[Source: Dallas Business Journal Online] AT&T is making some deeper cuts with its employee base. The Dallas telecommunications and media company shrunk the number of employees by 8,720 in the third quarter to less than 235,000, according to information on its investor relations website. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=DALBJO0020201026egap00001",
"source_name": "Factiva",
"pub_date": "2020-10-25T00:00:00Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 23058,
"name": "Covid-19",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 17963,
"name": "AT&T Inc.",
"logo": "//112233.contify.com/client_data/custom_tag/logo/a7jsrall_400x400-17963.png"
}
],
"Duns Number": [
{
"id": 18497,
"name": "108024050",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [
{
"id": 20102641627014,
"title": "AT&T cuts more than 8,700 workers in third quarter amid Covid-19",
"summary": "[Source: Louisville Business First Online] AT&T is making some deeper cuts with its employee base. The Dallas telecommunications and media company shrunk the number of employees by 8,720 in the third quarter to less than 235,000, according to information on its investor relations website. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=BSFLVO0020201026egaq00001",
"source_name": "Factiva"
},
{
"id": 20102741677548,
"title": "AT&T cuts more than 8,700 workers in third quarter amid Covid-19",
"summary": "[Source: Orlando Business Journal] AT&T is making some deeper cuts with its employee base. The Dallas-based telecommunications and media company (NYSE: T) shrunk the number of its employees by 8,720 in the third quarter to less than 235,000, according to information on its investor relations website. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=ORBJ000020201026egaq0005m",
"source_name": "Factiva"
}
],
"duplicate_count": 2
},
{
"id": 20102641660233,
"title": "Lufthansa considers cutting 30,000 jobs due to fall in air traffic",
"summary": "[Source: French Collection] German airline Lufthansa is facing a fall in air traffic due to the Covid-19 pandemic and consequently up to 30,000 jobs are threatened, its management said. Lufthansa is determined to keep at least 100,000 of the 130,000 positions in total at the group, Carsten Spohr, CEO of the airline, explained. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=FRECOL0020201026egaq000jh",
"source_name": "Factiva",
"pub_date": "2020-10-26T00:00:00Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 18140,
"name": "Deutsche Lufthansa AG",
"logo": "//112233.contify.com/client_data/custom_tag/logo/-99kuzpm_400x400-18140.jpg"
},
{
"id": 35588,
"name": "Lufthansa Group",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Duns Number": [
{
"id": 18584,
"name": "315000893",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [],
"duplicate_count": 0
},
{
"id": 20102541378844,
"title": "ExxonMobil chief warns US, Canada jobs cuts coming soon",
"summary": "[Source: Agence France Presse] Global oil giant ExxonMobil will release details soon of expected job cuts in the United States and Canada, the company's chief Darren Woods said in a letter to employees. \"As difficult as this is, I hope you understand it is critically important for the future of our company,\" Woods said in a statement Wednesday. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=AFPR000020201022egam00dqh",
"source_name": "Factiva",
"pub_date": "2020-10-22T13:04:58Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 15178,
"name": "Tax Risk",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 24236,
"name": "Exxon Mobil Corporation",
"logo": "//112233.contify.com/client_data/custom_tag/logo/ytocyynn_400x400-24236.jpg"
}
],
"Duns Number": [
{
"id": 24342,
"name": "1213214",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 34007,
"name": "001213214",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [],
"duplicate_count": 0
},
{
"id": 20102541377022,
"title": "Cathay Pacific to lay off 5,900 employees, close low-cost subsidiary",
"summary": "[Source: Kazinform International News Agency] HONG. KONG. KAZINFORM Hong Kong s flag carrier Cathay Pacific announced Wednesday it would lay off 5,900 employees and close low-cost subsidiary Cathay Dragon following corporate restructuring in response to the coronavirus s effect on aviation.«The restructuring will enable the company to secure its future, so it can protect as many jobs as possible, whilst meeting its responsibilities to the Hong Kong aviation hub and its customers,» the carrier said in a statement, EFE-EPA reports. ",
"source_url": "https://global.factiva.com/en/du/article.asp?accountid=9ERN000600&accessionno=KAZNAE0020201021egal000b7",
"source_name": "Factiva",
"pub_date": "2020-10-21T12:13:00Z",
"image_url": "",
"attachments": [],
"Triggers": [
{
"id": 15174,
"name": "Cost Reduction",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 15178,
"name": "Tax Risk",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 23058,
"name": "Covid-19",
"logo": "//112233.contify.com/images/tags-ico.png"
},
{
"id": 24196,
"name": "Restructuring",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Firms": [
{
"id": 35127,
"name": "Cathay Pacific Airways Ltd.",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Duns Number": [
{
"id": 33981,
"name": "686093188",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Channels": [
{
"id": 17,
"name": "News and Other Websites",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Content Types": [
{
"id": 3,
"name": "News Articles",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"Sources": [
{
"id": 68636,
"name": "Factiva",
"logo": "//112233.contify.com/images/tags-ico.png"
}
],
"previews": [],
"duplicates": [],
"duplicate_count": 0
}
]
}
解决方案
我建议您考虑调用存储过程活动而不是复制活动。然后解析 JSON 并将其存储在存储过程中。我在下面的答案中写下了这种方法。如果是单个 REST API 调用，您可以忽略 For 循环。我假设您的目标数据库是 SQL Server 或 Azure SQL 数据库，但如果不正确，请纠正我。
推荐阅读
- python - 有没有办法在熊猫数据框中的列之间动态地找到部分匹配数？
- javascript - 尝试对数据抓取进行身份验证时出现 Cookie 错误
- java - 面对异常HibernateException：实例的标识符
从 至 春天 - uwp - Xbox Series X 开发模式媒体捕获/远程查看
- python - 如何在 Python 中使用 selenium 单击按钮类
- flutter - 从一个由数字和逗号组成的字符串中返回整数列表
- node.js - 无服务器部署期间出现错误“无法读取未定义的属性‘名称’”
- checkout - 使用自托管代理解压缩时 GitHub Action 结帐失败
- c - 结构内存内容中的值是什么意思 - C
- javascript - 如何使用酶/玩笑根据图像加载返回的承诺测试内联样式更改？