'How do I parse Json file from azure blob storage to Azure Sql using Azure logic app
[][1]I have a multiple json files dropping in blob storage weekly, I want to use azure logic app to parse json file and copy data into Azure Sql? Please help
Solution 1:[1]
For achieving your requirement, Below are the flow that you can follow :-
Blob trigger (When a blob is added or modified (properties only) (V2)) >> Get blob content using path (V2) >> Parse JSON >> SQL related Action (For instance I'm using Insert row (V2)).
Below is the sample JSON that I'm uploading to my container.
{
"employees": {
"emp_name": "abc",
"hire_date": "2022-10-23",
"salary": 10000
}
}
I'm using triggers path to get the content of the blob. While Inserting the row I'm using Parse JSON values. Below is my Logic App.
Result:
UPDATED ANSWER
As per your requirement, you can either manually trigger flow using `` or set a recurrence trigger to make the flow triggered at times and then use list all files in that particular container from the storage account. Here is how the flow looks like
detailed flow
RESULTS:
code view of my logic app
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"For_each": {
"actions": {
"Get_blob_content_using_path_(V2)": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/GetFileContentByPath",
"queries": {
"inferContentType": true,
"path": "@items('For_each')?['Path']",
"queryParametersSingleEncoded": true
}
},
"runAfter": {},
"type": "ApiConnection"
},
"Insert_row_(V2)": {
"inputs": {
"body": {
"emp_id": "@body('Parse_JSON')?['employees']?['employee_id']",
"emp_name": "@body('Parse_JSON')?['employees']?['emp_name']",
"hire_date": "@body('Parse_JSON')?['employees']?['hire_date']",
"salary": "@body('Parse_JSON')?['employees']?['salary']"
},
"host": {
"connection": {
"name": "@parameters('$connections')['sql']['connectionId']"
}
},
"method": "post",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[employees]'))}/items"
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
},
"type": "ApiConnection"
},
"Parse_JSON": {
"inputs": {
"content": "@json(body('Get_blob_content_using_path_(V2)'))",
"schema": {
"properties": {
"employees": {
"properties": {
"emp_name": {
"type": "string"
},
"employee_id": {
"type": "integer"
},
"hire_date": {
"type": "string"
},
"salary": {
"type": "integer"
}
},
"type": "object"
}
},
"type": "object"
}
},
"runAfter": {
"Get_blob_content_using_path_(V2)": [
"Succeeded"
]
},
"type": "ParseJson"
}
},
"foreach": "@body('Lists_blobs_(V2)')?['value']",
"runAfter": {
"Lists_blobs_(V2)": [
"Succeeded"
]
},
"type": "Foreach"
},
"Lists_blobs_(V2)": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/foldersV2/@{encodeURIComponent(encodeURIComponent('JTJmY29udGFpbmVyMQ=='))}",
"queries": {
"nextPageMarker": "",
"useFlatListing": false
}
},
"metadata": {
"JTJmY29udGFpbmVyMQ==": "/container1"
},
"runAfter": {},
"type": "ApiConnection"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"manual": {
"inputs": {
"schema": {}
},
"kind": "Http",
"type": "Request"
}
}
},
"parameters": {
"$connections": {
"value": {
"azureblob": {
"connectionId": "/subscriptions/<SUBSCRIPTION ID>/resourceGroups/<RESOURCE GROUP NAME>/providers/Microsoft.Web/connections/azureblob",
"connectionName": "azureblob",
"id": "/subscriptions/<SUBSCRIPTION ID>/providers/Microsoft.Web/locations/centralus/managedApis/azureblob"
},
"sql": {
"connectionId": "/subscriptions/<SUBSCRIPTION ID>/resourceGroups/<RESOURCE GROUP NAME>/providers/Microsoft.Web/connections/sql",
"connectionName": "sql",
"id": "/subscriptions/<SUBSCRIPTION ID>/providers/Microsoft.Web/locations/centralus/managedApis/sql"
}
}
}
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |