含嵌套breakdown数组的API数据插入数据库的问题求解

尝试将API返回的嵌套数组数据插入数据库：主数组data包含id和type字段，部分条目带有包含label、percentage、count三列的breakdown嵌套数组。使用两层For_each循环（外层遍历data数组，内层遍历breakdown数组）执行插入时，因部分id无breakdown数组导致报错。添加以下空值处理表达式后，Logic App不再报错，但仅插入含breakdown数组的记录，无breakdown的条目被完全忽略：

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"
}
]
}

问题根源 #

之前的表达式将无breakdown的条目转为空数组，导致内层For_each循环无迭代项，而插入操作逻辑放在内层循环中，因此无breakdown的主条目完全没触发插入。

方案1：拆分主记录与明细记录的插入逻辑（推荐） #

将插入操作拆分为两步，确保所有主条目都被处理：

1. 外层For_each遍历data数组时，先插入主记录：仅插入id、type等基础字段，不管当前条目是否有breakdown。
2. 判断是否存在有效breakdown数组：如果breakdown不为空且非null，再启动内层For_each循环，遍历breakdown插入明细记录（需关联主记录的id）。

这种方式逻辑清晰，符合数据库主从表的设计规范，也避免了不必要的默认值插入。

方案2：修改内层循环数据源，强制生成默认明细 #

如果业务要求必须通过内层循环完成所有插入（比如主记录和明细记录需在同一步提交），可以修改内层循环的数据源表达式，当breakdown为空或不存在时，生成一条带默认值的数组，确保内层循环至少执行一次：

if(
  or(
    equals(items('For_each')?['breakdown'], null),
    empty(items('For_each')?['breakdown'])
  ),
  json('[{"label": "", "percentage": null, "count": null}]'),
  items('For_each')?['breakdown']
)

根据实际业务需求，你可以调整默认值（比如percentage设为0、count设为0等），这样内层循环会执行一次插入，关联主条目id并填充默认的明细值。

内容的提问来源于stack exchange，提问作者user19307825