topic Copy Activity: JSON Array Mapping in Pipelines

Copy Activity: JSON Array Mapping

chris__1 — Fri, 07 Jul 2023 10:36:53 GMT

Hello,

I query an API using the Copy Activity in Data Pipelines. The JSON returned has the following schema -

I struggle to get the mapping right to import all rows to a table in a Fabric Datawarehouse:

{ "datasource": "25b10fba", "columns": [ "ID", "Name", "WQSD ID", "Year", ], "metadata": [ { "type": "LONG", "dataSourceId": "25b10fba", "maxLength": -1, "minLength": -1, "periodIndex": 0, "aggregated": false }, { "type": "STRING", "dataSourceId": "25b10fba", "maxLength": -1, "minLength": -1, "periodIndex": 0, "aggregated": false }, { "type": "LONG", "dataSourceId": "25b10fba", "maxLength": -1, "minLength": -1, "periodIndex": 0, "aggregated": false }, { "type": "STRING", "dataSourceId": "25b10fba", "maxLength": -1, "minLength": -1, "periodIndex": 0, "aggregated": false } ], "rows": [ [ 17432, "XXX", 123, "2023" ], [ 17433, "YYY", 456, "2022" ] ... ], "numRows": 5538, "numColumns": 4, "fromcache": false, "ADFWebActivityResponseHeaders": { "Transfer-Encoding": "chunked", "Connection": "keep-alive", "Vary": "Origin;Access-Control-Request-Method;Access-Control-Request-Headers;Accept-Encoding;User-Agent", "X-Content-Type-Options": "nosniff", "X-XSS-Protection": "1; mode=block", "Pragma": "no-cache", "X-Frame-Options": "DENY", "Strict-Transport-Security": "max-age=31536000; includeSubDomains", "Cache-Control": "no-store, must-revalidate, no-cache, max-age=0", "Date": "Thu, 06 Jul 2023 14:35:02 GMT", "Content-Type": "application/json; charset=utf-8", "Expires": "0" }, "executionDuration": 3 }

I do not need the COLUMNS or METADATA part - I would just like to import all the columns from the ROWS section into a table.

How do I have to set up the mapping in the Copy Activity to achieve this!

Thanks for any advice!

Re: Copy Activity: JSON Array Mapping

GraceGu — Mon, 10 Jul 2023 05:40:57 GMT

Can you check whether using explicit mapping like below can work, details refer to Schema and data type mapping in copy activity - Azure Data Factory & Azure Synapse | Microsoft Learn?

Re: Copy Activity: JSON Array Mapping

chris__1 — Mon, 10 Jul 2023 15:00:05 GMT

Thanks so much and thanks for the link to the documentation!

Re: Copy Activity: JSON Array Mapping

noahadmcdonald — Mon, 15 Jan 2024 19:52:58 GMT

The Advanced Editor and Collection Reference features do not exist in Microsoft Fabric Data Factory. They do exist in Azure Data Factory. I'm running into the same problem where essentially my json data cannot be mapped properly because the activity is not able to recognize the array data type in Fabric Data Factory like it can in Azure Data Factory.

Re: Copy Activity: JSON Array Mapping

RH205 — Wed, 28 Feb 2024 09:41:07 GMT

chris__1 Could you elaborate on how you solved this problem in Fabric?

Re: Copy Activity: JSON Array Mapping

mariussve1 — Tue, 09 Jul 2024 05:48:45 GMT

Hi chris__1 ,

Did you get this to work? Im having the same issue.

Marius 🙂