Forum Discussion

chris__1's avatar
chris__1
Advocate II
3 years ago
Solved

Copy Activity: JSON Array Mapping

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!

5 Replies