Is there any way to store json Array or json object in SQL as a string into Azure SQL datawarehouse or anything to convert the Array into string(in Azure Data Factory).

Question

Is there any way to store json Array or json object in SQL as a string or anything to convert the Array into string.

Currently I am using the Data factory to fetch the Azure API data and try to store the same into Azure SQL data warehouse, but some of the API, I am getting nested json Array and json Object. Below is the reference, how to handle that newPurchasesDetailsand adjustmentDetails

[
{
"id": "/providers/Microsoft.Billing/billingAccounts/xxxxxxxxx/billingPeriods/202003/providers/Microsoft.Consumption/balances",
"name": "xxxxxxx_202003",
"type": "Microsoft.Consumption/balances",
"properties": {
"currency": "EUR ",
"beginningBalance": 0.0,
"endingBalance": 0.0,
"newPurchases": 0.0,
"adjustments": 106.13,
"utilized": 106.13,
"serviceOverage": xxxx,
"chargesBilledSeparately": 0.0,
"totalOverage": xxxxx,
"totalUsage": xxxxxx,
"azureMarketplaceServiceCharges": xxxxxxxx,
"billingFrequency": "Month",
"priceHidden": false,
"newPurchasesDetails": [

  ],
  "adjustmentDetails": [
    {
      "name": "O8",
      "value": 11.68
    },
    {
      "name": "O8",
      "value": 94.45
    }
  ]
}

}
]

Answer 1

Hi @vijay saini ,

As of today, only one array can be flattened in a schema. Multiple arrays can be referenced—returned as one row containing all of the elements in the array. However, only one array can have each of its elements returned as individual rows. This is the current limitation with jsonPath.

However you can first convert json file with nested objects into CSV file using Logic App and then you can use the CSV file as input for Azure Data factory. Please refer below URL to understand how Logic App can be used to convert nested objects in json file to CSV.

"https://adatis.co.uk/converting-json-with-nested-arrays-into-csv-in-azure-logic-apps-by-using-array-variable/"

Ref - https://stackoverflow.com/a/58108419/10653466

Hope this helps.