json 将嵌套数组数据插入数据库表

尝试插入从API获取的数据;它是一个嵌套数组。主要的一个“数据”包括id和类型，在某些情况下有一个“细分”数组，其中包括3个cols，标签百分比和计数。
我想把这个直接插入数据库然而，它没有意识到，当运行每个循环（有2个循环，一个用于数据阵列，最后一个用于分解阵列）时，对于一些ID，分解阵列是不存在的。
已尝试添加以下表达式：

if
(
  equals(items('For_each')?['breakdown'], null), 
  json('[]'), 
  items('For_each')?['breakdown']
)

这已经停止了失败的逻辑应用程序，但它只是插入记录与细分数组只和忽略所有其他没有它。
解决这个问题的最佳方法是什么？
先谢谢你了。
附加信息，使用的负载示例：

{
"result_ok": true,
"total_count": 0,
"page": 1,
"total_pages": 1,
"results_per_page": 0,
"data": [
{
"id": 22,
"type": "INSTRUCTIONS"
},
{
"id": 26,
"type": "INSTRUCTIONS"
},
{
"id": 24,
"type": "INSTRUCTIONS"
},
{
"id": 25,
"type": "INSTRUCTIONS"
},
{
"id": 3,
"type": "IMAGE_SELECT",
"breakdown": [
{
"label": "Agree",
"percentage": "34.6",
"count": "256"
},
{
"label": "Neither agree nor disagree",
"percentage": "30.9",
"count": "229"
},
{
"label": "Strongly agree",
"percentage": "17.6",
"count": "130"
},
{
"label": "Disagree",
"percentage": "12.2",
"count": "90"
},
{
"label": "Strongly disagree",
"percentage": "4.7",
"count": "35"
}
],
"total responses": "740",
"sum": "0",
"average": 0,
"stdDev": null,
"min": null,
"max": null
},
{
"id": 6,
"type": "IMAGE_SELECT",
"breakdown": [
{
"label": "Agree",
"percentage": "41.9",
"count": "309"
},
{
"label": "Strongly agree",
"percentage": "25.4",
"count": "187"
},
{
"label": "Neither agree nor disagree",
"percentage": "19.8",
"count": "146"
},
{
"label": "Disagree",
"percentage": "9.6",
"count": "71"
},
{
"label": "Strongly disagree",
"percentage": "3.3",
"count": "24"
}
],
"total responses": "737",
"sum": "0",
"average": 0,
"stdDev": null,
"min": null,
"max": null
},
{
"id": 5,
"type": "IMAGE_SELECT",
"breakdown": [
{
"label": "Agree",
"percentage": "28.1",
"count": "208"
},
{
"label": "Neither agree nor disagree",
"percentage": "27.6",
"count": "204"
},
{
"label": "Strongly agree",
"percentage": "18.6",
"count": "138"
},
{
"label": "Disagree",
"percentage": "14.3",
"count": "106"
},
{
"label": "Strongly disagree",
"percentage": "11.4",
"count": "84"
}
],
"total responses": "740",
"sum": "0",
"average": 0,
"stdDev": null,
"min": null,
"max": null
},
{
"id": 7,
"type": "IMAGE_SELECT",
"breakdown": [
{
"label": "Agree",
"percentage": "39.1",
"count": "288"
},
{
"label": "Neither agree nor disagree",
"percentage": "23.7",
"count": "175"
},
{
"label": "Strongly agree",
"percentage": "23.5",
"count": "173"
},
{
"label": "Disagree",
"percentage": "9.6",
"count": "71"
},
{
"label": "Strongly disagree",
"percentage": "4.1",
"count": "30"
}
],
"total responses": "737",
"sum": "0",
"average": 0,
"stdDev": null,
"min": null,
"max": null
},
{
"id": 33,
"type": "IMAGE_SELECT",
"breakdown": [
{
"label": "Agree",
"percentage": "39.1",
"count": "286"
},
{
"label": "Strongly agree",
"percentage": "24.6",
"count": "180"
},
{
"label": "Neither agree nor disagree",
"percentage": "24.3",
"count": "178"
},
{
"label": "Disagree",
"percentage": "7.7",
"count": "56"
},
{
"label": "Strongly disagree",
"percentage": "4.4",
"count": "32"
}
],
"total responses": "732",
"sum": "0",
"average": 0,
"stdDev": null,
"min": null,
"max": null
},
{
"id": 12,
"type": "ESSAY"
},
{
"id": 13,
"type": "CHECKBOX",
"breakdown": [

],
"total responses": null,
"sum": null,
"average": null,
"stdDev": "0.00",
"min": "0.0",
"max": "0.0"
},
{
"id": 1,
"type": "INSTRUCTIONS"
}
]
}

实现这一点的一种方法是通过将缺少的属性添加到具有null/空值的当前项来修改具有属性的当前项。下面是我的逻辑应用程序的流程。

For each回路内的流量

结果：

下面是我的逻辑应用程序的完整代码视图

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "For_each": {
                "actions": {
                    "Current_Item": {
                        "inputs": "@union(outputs('Template'),items('For_each'))",
                        "runAfter": {},
                        "type": "Compose"
                    },
                    "Insert_row_(V2)": {
                        "inputs": {
                            "body": {
                                "average": "@outputs('Current_Item')['average']",
                                "breakdown": "@{string(outputs('Current_Item')['breakdown'])}",
                                "id": "@outputs('Current_Item')['id']",
                                "max": "@outputs('Current_Item')['max']",
                                "min": "@outputs('Current_Item')['min']",
                                "stdDev": "@outputs('Current_Item')['stdDev']",
                                "sum": "@outputs('Current_Item')['sum']",
                                "total_responses": "@outputs('Current_Item')['total responses']",
                                "type": "@{outputs('Current_Item')['type']}"
                            },
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['sql']['connectionId']"
                                }
                            },
                            "method": "post",
                            "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[data]'))}/items"
                        },
                        "runAfter": {
                            "Current_Item": [
                                "Succeeded"
                            ]
                        },
                        "type": "ApiConnection"
                    }
                },
                "foreach": "@json(triggerBody())['data']",
                "runAfter": {
                    "Template": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Template": {
                "inputs": {
                    "average": null,
                    "breakdown": [],
                    "max": "0.0",
                    "min": "0.0",
                    "stdDev": "0.00",
                    "sum": null,
                    "total responses": null
                },
                "runAfter": {},
                "type": "Compose"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "sql": {
                    "connectionId": "/subscriptions/<subid>/resourceGroups/<rg>/providers/Microsoft.Web/connections/sql",
                    "connectionName": "sql",
                    "id": "/subscriptions/<subid>/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                }
            }
        }
    }
}

json 将嵌套数组数据插入数据库表

1条答案

相关问题

热门标签

最新问答