Azure 数据工厂 V2 复制活动，其中 Rest API 为嵌套的 JSON 提供一行

Question

我正在尝试扁平化从 Rest 源返回的嵌套 JSON。管道代码如下。 这里的问题是此管道仅返回 JSON 数据集中的第一个对象，并跳过所有其他行。 您能否指导我如何迭代嵌套对象。

谢谢 萨米特

{
"name": "STG_NCR2",
"properties": {
"activities": [
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "RestSource",
"httpRequestTimeout": "00:01:40",
"requestInterval": "00.00:00:00.010",
"requestMethod": "GET",
"additionalHeaders": {
"OData-MaxVersion": "4.0",
"OData-Version": "4.0",
"Prefer": "odata.include-annotations=*"
}
},
"sink": {
"type": "AzureSqlSink"
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "$['value'][0]['tco_ncrid']"
},
"sink": {
"name": "NCRID"
}
},
{
"source": {
"path": "['tco_name']"
},
"sink": {
"name": "EquipmentSerialNumber"
}
}
],
"collectionReference": "$['value'][0]['tco_ncr_tco_equipment']"
}
},
"inputs": [
{
"referenceName": "Rest_PowerApps_NCR",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Prestaging_PowerApps_NCREquipments",
"type": "DatasetReference"
}
]
}
],
"annotations": []
}
}

JSON 采用以下格式

[ 
{ 
"value":[ 
{ 
"tco_ncrid":"abc-123",
"tco_ncr_tco_equipment":[ 
{ 
"tco_name":"abc"
}
]
},
{ 
"tco_ncrid":"abc-456",
"tco_ncr_tco_equipment":[ 
{ 
"tco_name":"xyz"
},
{ 
"tco_name":"yzx"
}
}
]
]
}
]

Answer 1

这可以通过修改转换器属性来解决，如下所示。

"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "$.['value'][0].['tco_ncrid']"
},
"sink": {
"name": "NCRID",
"type": "String"
}
},
{
"source": {
"path": "$.['value'][0].['tco_text_id']"
},
"sink": {
"name": "EquipmentDescription",
"type": "String"
}
},
{
"source": {
"path": "['tco_name']"
},
"sink": {
"name": "EquipmentSerialNumber",
"type": "String"
}
}
],
"collectionReference": "$.['value'][*].['tco_ncr_tco_equipment']"
}

此代码强制管道遍历嵌套数组，但如您所见，NCRID 被硬编码为值数组的第一个元素。这不是我想要的，因为我正在寻找每个NCRID的所有设备序列号。还在研究...