Execute SQL query to import JSON

(0) Share

Report

Posted on by chh101

Hello,

I am having trouble executing a SQL query to import a JSON file queried from an API.

I used the "Parse JSON" step on the body of the API-Response.

The step to execute the query looks like this:

The formula used as input to OPENJSON is

@{body('Parse_JSON')?['response']}

So I think that returns all "response" object in the json.

The json Schema looks like this:

"body": {
 "type": "object",
 "properties": {
 "response": {
 "type": "object",
 "properties": {
 "results": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "date": {
 "type": "string"
 },
 "line_item": {
 "type": "string"
 },
 "ID": {
 "type": "integer"
 },
 "discounts": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "discountid": {
 "type": "integer"
 },
 "discount_name": {
 "type": "string"
 }
 },
 "required": [
 "discountid",
 "discount_name",
 ]
 }
 },
 "secondid": {
 "type": "integer"
 },
 "second_name": {
 "type": "string"
 }
 },
 "required": [
 "date",
 "line_item",
 "ID",
 ]
 }
 },
 "total_1": {
 "type": "string"
 },
 "total_2": {
 "type": "string"
 },
 "total_3": {
 "type": "string"
 },
 "gross_3": {
 "type": "string"
 },
 "gross_2": {
 "type": "string"
 },
 "net_1": {
 "type": "string"
 },
 "net_3": {
 "type": "string"
 }
 }
 }
 }
 }

I found this on using OPENJSON() to import json to SQL Server: https://database.guide/how-to-insert-json-into-a-table-in-sql-server/

There must be a problem regarding quotation marks as I get an error for every single colon that is included in string fields of the json file. The final line of the Power Automate error says

Unclosed quotation mark after the character string '}');'.

Any help is much appreciated!

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

takolota1 4,980 Moderator on at

Like (0)

Report
Copy link

Link copied!

@chh101

There are at least a couple ways to do batch create, read, update, & delete in SQL using Power Automate…

With more SQL code & calling the stored procedure in Power Automate:

https://garrytrinder.github.io/2019/03/bulk-insert-array-of-json-objects-into-azure-sql-database-using-microsoft-flow

With more actions directly in a Power Automate flow:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/td-p/1715338

Was this reply helpful? Yes No
chh101 13 on at

Like (1)

Report
Copy link

Link copied!

Thank you @takolota for your answer. Both articles are very helpful and comprehensive.

However, the first solution requires a datasource to store the json file to. They used a blob storage. Providing a place to store the file to - that the sql server can use as datasource - is an addition step I do not have an immediate solution to.
If I understand the second solution correctly it will require transforming the json to csv to then import this output to the server. SQL Server would offer a possibility via OPENJSON to use json files directly which should be beneficial compared to constructing a csv file on top.

I was hoping to do as explained here: https://powerusers.microsoft.com/t5/Building-Flows/Execute-SQL-Query-Insert-json-output-to-table/m-p/901121
This is again referencing this explanation of OPENJSON: https://stackoverflow.com/questions/46323946/how-to-insert-json-object-to-sql-server-2016-as-nvarchar

I suspect that either my json is parsed incorrectly by my sample or my instructions in the "Execute a SQL query V2" are faulty.

If this would work, I suspect it to be a very efficient way of importing thousands of lines via Power Automate to SQL Server!

Was this reply helpful? Yes No