Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Struggling with getting ADF to transform JSON into something I can pass to SQLDB
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(217.60000000000002, 56.00000000000001%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJB%3C/text%3E%3C/svg%3E)
Jeff Bunker
0
Reputation points
Hey folks, so I currently have an API pulling data from a SaaS application and saving the JSON into a blob in ADF, but I am now trying to transform this data into a table that will be passed into SQLDB. It is a paged API with max records returned as 25 so I have a total of 401 pages of 25 records all merged into this single JSON. I can get a data flow to separate the two columns, one is columns and one is row down to the individual items (in green) in separate streams, but how do I get these both to relate and the correct information in the correct column? As it currently is just a column of columns and a column of rows. If I flatten the rows one less it maintains an array for each row, but then won't map to the columns. I feel like there is an easy answer here and I've just over thought it.
Azure Data Factory
{count} votes
-
Jeff Bunker 0 Reputation points
2023-03-27T05:51:35.1333333+00:00 I also ran the file through a JSON validator and it returns valid JSON.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator2023-03-27T23:57:14.64+00:00 Hi @Jeff Bunker ,
Welcome to Microsoft Q&A forum and thanks for reaching out here.
Could you please help attach (please remove any sensitive information and save it as .txt and attach here) a sample JSON file for the above sample you provided as image, so that we can use it for testing and providing relevant solution.
We look forward to your response.
Thank you
-
Jeff Bunker 0 Reputation points
2023-03-28T21:37:28.33+00:00 -
Jeff Bunker 0 Reputation points
2023-03-28T21:38:30.6833333+00:00 [ { "columns": [ "Unique ID", "Record Organization", "Record Location", "Created At", "Updated At", "Workflow", "Indicator Set: Incident Status", "Indicator Set: Type of Incident", "Indicator Set: Recordable Incidents", "Indicator Set: Loss Work Time", "Indicator Set: Body Part Injured by Incident", "Indicator Set: Cause of Incident", "Indicator Set: Classification of Incident", "Created By", "Link to Record" ], "rows": [ [ "INC0502", "Contract Logistics Group", "Carson", "04/01/2020 01:13 PM", "08/12/2021 05:11 PM", "Closed", "Closed", null, "No", null, null, null, null, "Randy", "https://company.us.donesafe.com/module_records/502" ], [ "INC0506", "Contract Logistics Group", "Carson", "04/01/2020 02:49 PM", "10/14/2021 10:48 AM", "Closed", "Closed", null, "No", null, null, null, null, "Joni", "https://company.us.donesafe.com/module_records/506" ], [ "INC0507", "Contract Logistics Group", "Dominguez", "04/01/2020 06:57 PM", "10/09/2021 04:14 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/507" ], [ "INC0508", "Contract Logistics Group", "Dominguez", "04/01/2020 11:39 PM", "08/11/2021 11:13 PM", "Closed", "Closed", null, "No", null, null, "Dust particle", null, "Fili", "https://company.us.donesafe.com/module_records/508" ], [ "INC0509", "Contract Logistics Group", "Carson 3", "04/02/2020 12:01 AM", "08/12/2021 08:41 AM", "Closed", "Closed", null, "No", null, null, null, null, "Kyle", "https://company.us.donesafe.com/module_records/509" ], [ "INC0510", "Contract Logistics Group", "Dominguez", "04/02/2020 08:48 PM", "10/09/2021 04:14 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/510" ], [ "INC0511", "Contract Logistics Group", "Dominguez", "04/02/2020 11:48 PM", "08/11/2021 11:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Jennifer", "https://company.us.donesafe.com/module_records/511" ], [ "INC0512", "Contract Logistics Group", "Sumner", "04/04/2020 12:21 AM", "08/06/2021 11:17 PM", "Closed", "Closed", null, "No", null, null, null, null, "Laura", "https://company.us.donesafe.com/module_records/512" ], [ "INC0513", "Contract Logistics Group", "Dominguez", "04/04/2020 12:51 AM", "08/12/2021 08:41 AM", "Closed", "Closed", null, "No", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/513" ], [ "INC0514", "Contract Logistics Group", "Dominguez", "04/04/2020 03:38 PM", "08/12/2021 08:42 AM", "Closed", "Closed", null, "No", null, null, null, null, "Andy", "https://company.us.donesafe.com/module_records/514" ], [ "INC0515", "International Freight Forwarding", "Chicago", "04/06/2020 04:37 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Kevin", "https://company.us.donesafe.com/module_records/515" ], [ "INC0516", "Contract Logistics Group", "Dominguez", "04/07/2020 12:26 AM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, "Push / Pulling", null, "Fili", "https://company.us.donesafe.com/module_records/516" ], [ "INC0519", "Contract Logistics Group", "Sumner", "04/07/2020 02:43 PM", "10/14/2021 10:48 AM", "Closed", "Closed", "Injury/Illness", "Yes", null, "Head/Face/Eyes", "Struck by object", "Restricted Duty", "Michael", "https://company.us.donesafe.com/module_records/519" ], [ "INC0520", "Contract Logistics Group", "Sumner", "04/07/2020 06:39 PM", "01/09/2023 10:29 AM", "Closed", "Closed", "Injury/Illness", "Yes", "Yes", "Hand/Wrist", "Other", "Loss Work Time", "Michael", "https://company.us.donesafe.com/module_records/520" ], [ "INC0521", "Contract Logistics Group", "Sumner", "04/07/2020 07:44 PM", "01/09/2023 11:47 AM", "Closed", "Closed", "Injury/Illness", "Yes", "Yes", null, "Struck by PIT", "Loss Work Time", "Michael", "https://company.us.donesafe.com/module_records/521" ], [ "INC0522", "Contract Logistics Group", "Dominguez", "04/07/2020 10:29 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/522" ], [ "INC0523", "Contract Logistics Group", "Dominguez", "04/07/2020 10:47 PM", "10/09/2021 04:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/523" ], [ "INC0525", "Contract Logistics Group", "Dominguez", "04/08/2020 02:34 PM", "08/06/2021 11:16 PM", "Closed", "Closed", null, "No", null, null, null, null, "Abel", "https://company.us.donesafe.com/module_records/525" ], [ "INC0526", "Contract Logistics Group", "Dominguez", "04/08/2020 11:31 PM", "08/11/2021 11:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Jennifer", "https://company.us.donesafe.com/module_records/526" ], [ "INC0527", "Contract Logistics Group", "Carson 3", "04/09/2020 03:39 AM", "08/12/2021 08:41 AM", "Closed", "Closed", null, "No", null, null, null, null, "Kyle", "https://company.us.donesafe.com/module_records/527" ], [ "INC0528", "Contract Logistics Group", "Carson 3", "04/09/2020 09:34 AM", "08/12/2021 05:08 PM", "Closed", "Closed", null, "Yes", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/528" ], [ "INC0529", "Contract Logistics Group", "Carson 3", "04/09/2020 10:06 AM", "08/12/2021 05:08 PM", "Closed", "Closed", "Injury/Illness", "Yes", null, null, null, "Restricted Duty", "Michael", "https://company.us.donesafe.com/module_records/529" ], [ "INC0530", "Contract Logistics Group", "Carson 3", "04/09/2020 10:32 AM", "08/12/2021 05:08 PM", "Closed", "Closed", null, "Yes", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/530" ], [ "INC0532", "Contract Logistics Group", "Dominguez", "04/09/2020 12:51 PM", "10/09/2021 04:15 PM", "Closed", "Closed", null, "Yes", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/532" ], [ "INC0533", "Contract Logistics Group", "Dominguez", "04/09/2020 01:40 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "Yes", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/533" ] ] }, { "columns": [ "Unique ID", "Record Organization", "Record Location", "Created At", "Updated At", "Workflow", "Indicator Set: Incident Status", "Indicator Set: Type of Incident", "Indicator Set: Recordable Incidents", "Indicator Set: Loss Work Time", "Indicator Set: Body Part Injured by Incident", "Indicator Set: Cause of Incident", "Indicator Set: Classification of Incident", "Created By", "Link to Record" ], "rows": [ [ "INC0534", "Yusen Logistics Americas", "Carson", "04/09/2020 04:41 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Esmeralda", "https://company.us.donesafe.com/module_records/534" ], [ "INC0536", "Contract Logistics Group", "Dominguez", "04/09/2020 05:47 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/536" ], [ "INC0537", "Contract Logistics Group", "Dominguez", "04/10/2020 02:07 AM", "08/12/2021 08:41 AM", "Closed", "Closed", null, "No", null, null, null, null, "Michael", "https://company.us.donesafe.com/module_records/537" ], [ "INC0538", "Contract Logistics Group", "Dominguez", "04/10/2020 03:37 AM", "10/09/2021 04:14 PM", "Closed", "Closed", null, "No", null, null, null, null, "Jennifer", "https://company.us.donesafe.com/module_records/538" ], [ "INC0539", "Contract Logistics Group", "Dominguez", "04/10/2020 01:47 PM", "08/12/2021 08:42 AM", "Closed", "Closed", null, "No", null, null, null, null, "Andy", "https://company.us.donesafe.com/module_records/539" ], [ "INC0541", "Contract Logistics Group", "Rogers", "04/13/2020 11:18 AM", "08/11/2021 11:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Theresa", "https://company.us.donesafe.com/module_records/541" ], [ "INC0542", "Contract Logistics Group", "Dominguez", "04/13/2020 11:19 AM", "06/03/2021 03:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Chip", "https://company.us.donesafe.com/module_records/542" ], [ "INC0543", "Contract Logistics Group", "Sumner", "04/13/2020 01:31 PM", "06/03/2021 03:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Steve", "https://company.us.donesafe.com/module_records/543" ], [ "INC0544", "Contract Logistics Group", "Dominguez", "04/13/2020 03:05 PM", "08/06/2021 11:17 PM", "Closed", "Closed", null, "No", null, null, null, null, "Joni", "https://company.us.donesafe.com/module_records/544" ], [ "INC0545", "International Freight Forwarding", "Los Angeles", "04/13/2020 05:13 PM", "08/06/2021 11:16 PM", "Closed", "Closed", "Injury/Illness", "Yes", null, "Back", "Accident involving moving motor vehicle", "Restricted Duty", "Alex", "https://company.us.donesafe.com/module_records/545" ], [ "INC0546", "Contract Logistics Group", "Dominguez", "04/13/2020 09:38 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Fili", "https://company.us.donesafe.com/module_records/546" ], [ "INC0562", "Contract Logistics Group", "Dominguez", "04/13/2020 10:47 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/562" ], [ "INC0563", "Contract Logistics Group", "Dominguez", "04/14/2020 12:29 AM", "10/09/2021 04:14 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/563" ], [ "INC0564", "Contract Logistics Group", "Dominguez", "04/14/2020 04:48 AM", "08/11/2021 11:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Fili", "https://company.us.donesafe.com/module_records/564" ], [ "INC0566", "Contract Logistics Group", "Dominguez", "04/14/2020 10:46 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/566" ], [ "INC0567", "Contract Logistics Group", "Dominguez", "04/15/2020 12:06 AM", "10/09/2021 04:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/567" ], [ "INC0570", "International Freight Forwarding", "Atlanta", "04/15/2020 01:41 PM", "06/03/2021 03:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Charles", "https://company.us.donesafe.com/module_records/570" ], [ "INC0571", "Contract Logistics Group", "Carson 3", "04/15/2020 10:20 PM", "08/12/2021 08:41 AM", "Closed", "Closed", null, "No", null, null, null, null, "Kyle", "https://company.us.donesafe.com/module_records/571" ], [ "INC0572", "Contract Logistics Group", "Dominguez", "04/15/2020 10:58 PM", "06/03/2021 03:15 PM", "Closed", "Closed", null, "No", null, null, null, null, "Nicholas", "https://company.us.donesafe.com/module_records/572" ], [ "INC0573", "Contract Logistics Group", "Dominguez", "04/15/2020 11:23 PM", "10/09/2021 04:13 PM", "Closed", "Closed", null, "No", null, null, null, null, "Juan", "https://company.us.donesafe.com/module_records/573" ], [ "INC0575", "Contract Logistics Group", "Waco", "04/16/2020 08:37 AM", "09/16/2021 02:41 PM", "Closed", "Closed", null, "No", null, null, null, null, "Geoffrey", "https://company.us.donesafe.com/module_records/575" ], [ "INC0576", "Contract Logistics Group", "Carson 3", "04/16/2020 10:53 AM", "09/15/2021 08:58 AM", "Closed", "Closed", null, "No", null, null, null, null, "Alejandro", "https://company.us.donesafe.com/module_records/576" ], [ "INC0577", "Contract Logistics Group", "Carson", "04/16/2020 11:37 AM", "09/15/2021 08:58 AM", "Closed", "Closed", null, "No", null, null, null, null, "Alejandro", "https://company.us.donesafe.com/module_records/577" ], [ "INC0578", "Contract Logistics Group", "Carson", "04/16/2020 12:00 PM", "09/15/2021 08:58 AM", "Closed", "Closed", null, "No", null, null, null, null, "Alejandro", "https://company.us.donesafe.com/module_records/578" ], [ "INC0580", "Contract Logistics Group", "Dominguez", "04/16/2020 03:00 PM", "09/15/2021 08:58 AM", "Closed", "Closed", null, "No", null, null, null, null, "Jesus (WHSE Supervisor)", "https://company.us.donesafe.com/module_records/580" ] ] },' -
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-03-31T22:32:36.4166667+00:00 @Jeff Bunker Thank you for sharing the sample data. I'll look into it and will get back to you as make some progress. Thank you agian!
Sign in to comment
Sign in to answer