需要在Azure Data Factory中生成具有动态键的JSON文件。

huangapple
go评论87阅读模式
英文:

Need to generate JSON file in Azure Data Factory with dynamic Key

问题

以下是翻译好的内容：

我在CSV文件中有以下数据。

ID userId Name
719A070E-4874-E811-9CCE-02152146006A 123 Joe
5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0 456 Mike

现在，我需要使用Azure数据工厂生成以下JSON，

  1. [
  2.     {
  3.         "719A070E-4874-E811-9CCE-02152146006A":{
  4.                  "userId":"123",
  5.                   "Name":"Joe"
  6.              }    
  7.     },
  8.     {
  9.         "5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0":{
  10.                  "userId":"456",
  11.                   "Name":"Mike"
  12.              }    
  13.     }
  14.  ]

我已经尝试了所有可能的方法，我能够创建内部JSON，使用用户ID和姓名。但我无法弄清楚如何设置JSON中的动态键，该键必须从CSV中的ID列派生。

英文:

I have a below data in csv file.

ID userId Name
719A070E-4874-E811-9CCE-02152146006A 123 Joe
5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0 456 Mike

Now, I need to generate below json using Azure Data Factory,

  1. [
  2.     {
  3.         "719A070E-4874-E811-9CCE-02152146006A":{
  4.                  "userId":"123",
  5.                   "Name":"Joe"
  6.              }    
  7.     },
  8.     {
  9.         "5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0":{
  10.                  "userId":"456",
  11.                   "Name":"Mike"
  12.              }    
  13.     }
  14.  ]

I have tried all the possible ways, I can able to form the inner json using userid and name. But I cannot able to figure out how to set the dynamic KEY in JSON which has to be derived from ID column in csv.

答案1

得分: 1

我可以使用ForEach和附加变量活动来实现您的要求。

我采用了与您的CSV相同的数据并将其提供给了一个查找活动（在查找活动中取消选中“第一行”复选框）。在CSV数据集中，请取消选中“第一行作为标题”的复选框。

这将是查找输出数组：

需要在Azure Data Factory中生成具有动态键的JSON文件。

然后，在变量中创建一个数组变量。创建一个ForEach活动，并为其提供以下表达式（在这里选中“Sequential”复选框）：

  1. @skip(activity('Lookup1').output.value, 1)

在ForEach内部，创建一个附加变量活动，其中包含以下表达式：

  1. @json(concat('{"', item().Prop_0, '":{"', activity('Lookup1').output.value[0].Prop_1, '":"', item().Prop_1, '","', activity('Lookup1').output.value[0].Prop_2, '":"', item().Prop_2, '"}}'))

在这里，上述表达式在每次迭代中生成对象并附加到数组变量中（这是结果数组）。

为了显示输出，我使用了另一个数组变量，并在ForEach之后将结果数组的值分配给它。

需要在Azure Data Factory中生成具有动态键的JSON文件。

您可以参考以下是我的管道JSON：

  1. {
  2.     "name": "pipeline1",
  3.     "properties": {
  4.         "activities": [
  5.             {
  6.                 "name": "Lookup1",
  7.                 "type": "Lookup",
  8.                 "dependsOn": [],
  9.                 "policy": {
  10.                     "timeout": "0.12:00:00",
  11.                     "retry": 0,
  12.                     "retryIntervalInSeconds": 30,
  13.                     "secureOutput": false,
  14.                     "secureInput": false
  15.                 },
  16.                 "userProperties": [],
  17.                 "typeProperties": {
  18.                     "source": {
  19.                         "type": "DelimitedTextSource",
  20.                         "storeSettings": {
  21.                             "type": "AzureBlobFSReadSettings",
  22.                             "recursive": true,
  23.                             "enablePartitionDiscovery": false
  24.                         },
  25.                         "formatSettings": {
  26.                             "type": "DelimitedTextReadSettings"
  27.                         }
  28.                     },
  29.                     "dataset": {
  30.                         "referenceName": "sourcecsv",
  31.                         "type": "DatasetReference"
  32.                     },
  33.                     "firstRowOnly": false
  34.                 }
  35.             },
  36.             {
  37.                 "name": "ForEach1",
  38.                 "type": "ForEach",
  39.                 "dependsOn": [
  40.                     {
  41.                         "activity": "Lookup1",
  42.                         "dependencyConditions": [
  43.                             "Succeeded"
  44.                         ]
  45.                     }
  46.                 ],
  47.                 "userProperties": [],
  48.                 "typeProperties": {
  49.                     "items": {
  50.                         "value": "@skip(activity('Lookup1').output.value, 1)",
  51.                         "type": "Expression"
  52.                     },
  53.                     "isSequential": true,
  54.                     "activities": [
  55.                         {
  56.                             "name": "Append variable1",
  57.                             "type": "AppendVariable",
  58.                             "dependsOn": [],
  59.                             "userProperties": [],
  60.                             "typeProperties": {
  61.                                 "variableName": "arr",
  62.                                 "value": {
  63.                                     "value": "@json(concat('{"', item().Prop_0, '":{"', activity('Lookup1').output.value[0].Prop_1, '":"', item().Prop_1, '","', activity('Lookup1').output.value[0].Prop_2, '":"', item().Prop_2, '"}}'))",
  64.                                     "type": "Expression"
  65.                                 }
  66.                             }
  67.                         }
  68.                     ]
  69.                 }
  70.             },
  71.             {
  72.                 "name": "Set variable1",
  73.                 "type": "SetVariable",
  74.                 "dependsOn": [
  75.                     {
  76.                         "activity": "ForEach1",
  77.                         "dependencyConditions": [
  78.                             "Succeeded"
  79.                         ]
  80.                     }
  81.                 ],
  82.                 "userProperties": [],
  83.                 "typeProperties": {
  84.                     "variableName": "newarr",
  85.                     "value": {
  86.                         "value": "@variables('arr')",
  87.                         "type": "Expression"
  88.                     }
  89.                 }
  90.             }
  91.         ],
  92.         "variables": {
  93.             "arr": {
  94.                 "type": "Array"
  95.             },
  96.             "newarr": {
  97.                 "type": "Array"
  98.             }
  99.         },
  100.         "annotations": []
  101.     }
  102. }
英文:

I am able to achieve your requirement using ForEach and append variable activities.

I took csv same data like yours and given it to a lookup activity(uncheck the first row in lookup activity). Here in the csv dataset, uncheck the first row as header checkbox.

This will be the lookup output array:

需要在Azure Data Factory中生成具有动态键的JSON文件。

Then, in the variables create an array variable. Create a ForEach activity and give the following expression for it(check the Sequential checkbox here).

  1. @skip(activity('Lookup1').output.value, 1)

Inside ForEach, create an append variable activity with following expression in it.

  1. @json(concat('{"',item().Prop_0,'":{"',activity('Lookup1').output.value[0].Prop_1,'":"',item().Prop_1,'","',activity('Lookup1').output.value[0].Prop_2,'":"',item().Prop_2,'"}}'))

需要在Azure Data Factory中生成具有动态键的JSON文件。

Here, the above expression generates the object in each iteration and appends to the array variable(This is result array).

For showing the output, I have used another array variable and assigned the result array value to it after Foreach.

需要在Azure Data Factory中生成具有动态键的JSON文件。

My Pipeline JSON for your reference:

  1. {
  2. "name": "pipeline1",
  3. "properties": {
  4. "activities": [
  5. {
  6. "name": "Lookup1",
  7. "type": "Lookup",
  8. "dependsOn": [],
  9. "policy": {
  10. "timeout": "0.12:00:00",
  11. "retry": 0,
  12. "retryIntervalInSeconds": 30,
  13. "secureOutput": false,
  14. "secureInput": false
  15. },
  16. "userProperties": [],
  17. "typeProperties": {
  18. "source": {
  19. "type": "DelimitedTextSource",
  20. "storeSettings": {
  21. "type": "AzureBlobFSReadSettings",
  22. "recursive": true,
  23. "enablePartitionDiscovery": false
  24. },
  25. "formatSettings": {
  26. "type": "DelimitedTextReadSettings"
  27. }
  28. },
  29. "dataset": {
  30. "referenceName": "sourcecsv",
  31. "type": "DatasetReference"
  32. },
  33. "firstRowOnly": false
  34. }
  35. },
  36. {
  37. "name": "ForEach1",
  38. "type": "ForEach",
  39. "dependsOn": [
  40. {
  41. "activity": "Lookup1",
  42. "dependencyConditions": [
  43. "Succeeded"
  44. ]
  45. }
  46. ],
  47. "userProperties": [],
  48. "typeProperties": {
  49. "items": {
  50. "value": "@skip(activity('Lookup1').output.value, 1)",
  51. "type": "Expression"
  52. },
  53. "isSequential": true,
  54. "activities": [
  55. {
  56. "name": "Append variable1",
  57. "type": "AppendVariable",
  58. "dependsOn": [],
  59. "userProperties": [],
  60. "typeProperties": {
  61. "variableName": "arr",
  62. "value": {
  63. "value": "@json(concat('{\"',item().Prop_0,'\":{\"',activity('Lookup1').output.value[0].Prop_1,'\":\"',item().Prop_1,'\",\"',activity('Lookup1').output.value[0].Prop_2,'\":\"',item().Prop_2,'\"}}'))",
  64. "type": "Expression"
  65. }
  66. }
  67. }
  68. ]
  69. }
  70. },
  71. {
  72. "name": "Set variable1",
  73. "type": "SetVariable",
  74. "dependsOn": [
  75. {
  76. "activity": "ForEach1",
  77. "dependencyConditions": [
  78. "Succeeded"
  79. ]
  80. }
  81. ],
  82. "userProperties": [],
  83. "typeProperties": {
  84. "variableName": "newarr",
  85. "value": {
  86. "value": "@variables('arr')",
  87. "type": "Expression"
  88. }
  89. }
  90. }
  91. ],
  92. "variables": {
  93. "arr": {
  94. "type": "Array"
  95. },
  96. "newarr": {
  97. "type": "Array"
  98. }
  99. },
  100. "annotations": []
  101. }
  102. }

huangapple
  • 本文由 发表于 2023年5月14日 01:37:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/76244108.html
  • azure
  • azure-data-factory
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定