Copy JSON Array data from REST data factory to Azure Blob as is

Question

I have used REST to get data from API and the format of JSON output that contains arrays. When I am trying to copy the JSON as it is using copy activity to BLOB, I am only getting first object data and the rest is ignored.

In the documentation is says we can copy JSON as is by skipping schema section on both dataset and copy activity. I followed the same and I am the getting the output as below.

https://docs.microsoft.com/en-us/azure/data-factory/connector-rest#export-json-response-as-is

Tried copy activity without schema, using the header as first row and output files to BLOB as .json and .txt

Sample REST output:

{
  "totalPages": 500,
  "firstPage": true,
  "lastPage": false,
  "numberOfElements": 50,
  "number": 0,
  "totalElements": 636,
  "columns": {
    "dimension": {
      "id": "variables/page",
      "type": "string"
    },
    "columnIds": [
      "0"
    ]
  },
  "rows": [
    {
      "itemId": "1234",
      "value": "home",
      "data": [
        65
      ]
    },
    {
      "itemId": "1235",
      "value": "category",
      "data": [
        92
      ]
    },
    ],
  "summaryData": {
    "totals": [
      157
    ],
    "col-max": [
      123
    ],
    "col-min": [
      1
    ]
  }
}

BLOB Output as the text is below: which is only first object data

totalPages,firstPage,lastPage,numberOfElements,number,totalElements
500,True,False,50,0,636

chiragMishra-msft chiragMishra-msft · Accepted Answer · 2019-08-01T07:11:05

If you want to write the JSON response as is, you can use an HTTP connector. However, please note that the HTTP connector doesn't support pagination.

If you want to keep using the REST connector and to write a csv file as output, can you please specify how you want the nested objects and arrays to be written ?

In csv files, we can not write arrays. You could always use a custom activity or an azure function activity to call the REST API, parse it the way you want and write to a csv file.

Hope this helps.

Copy JSON Array data from REST data factory to Azure Blob as is

1 Answers