处理API返回含空格空数组的SQL批量插入问题求助

针对你遇到的API返回[ ]（全空格数组）导致SQL存储过程无法处理的问题，提供两种可行方案：

方案1：在Logic App中预处理JSON（推荐） #

在调用存储过程之前，先通过Logic App的Select动作遍历所有记录，将全空格数组替换为[""]，避免在SQL层做复杂处理。

具体步骤： #

1. 添加Select动作，选择HTTP请求返回的records数组作为输入。
2. 在映射规则中，对roleProfiles和emailAddresses字段使用表达式处理：
   • 处理roleProfiles的表达式：

     if(equals(join(item()?['roleProfiles'], ''), ''), '[""]', item()?['roleProfiles'])
     

   • 处理emailAddresses的表达式和上面一致，替换字段名即可。
3. 将Select动作的输出作为存储过程的输入参数。

说明：join(item()?['roleProfiles'], '')会把数组元素拼接成字符串，全空格数组拼接后是空字符串，此时替换为[""]；正常数组则保留原内容。

方案2：在SQL存储过程中处理JSON #

如果无法修改Logic App流程，可以在存储过程中先对输入的JSON进行预处理，替换全空格数组后再解析插入。

示例修改后的存储过程代码： #

假设原存储过程接收@json NVARCHAR(MAX)作为批量数据参数，修改如下：

CREATE PROCEDURE [dbo].[YourBatchInsertProc]
    @json NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 预处理JSON：替换所有[]之间仅含空格的数组为[""]
    -- SQL Server 2017及以上支持REGEXP_REPLACE，匹配任意数量空格
    SET @json = REGEXP_REPLACE(@json, '\[\s+\]', '[""]');

    -- 如果使用低于2017的版本，可精准匹配固定长度的空格（比如20个空格）
    -- SET @json = REPLACE(@json, '[                    ]', '[""]'); -- 20个空格

    -- 后续原有的解析和插入逻辑，使用处理后的@json
    INSERT INTO YourTargetTable (Column1, roleProfiles, emailAddresses, ...)
    SELECT 
        JSON_VALUE(item, '$.Column1'),
        JSON_QUERY(item, '$.roleProfiles'),
        JSON_QUERY(item, '$.emailAddresses'),
        ...
    FROM OPENJSON(@json, '$.records') AS j
    CROSS APPLY (SELECT j.value AS item) AS sub;
END
GO

说明：

• 若SQL Server版本低于2017，无法使用REGEXP_REPLACE，则需要精准匹配API返回的空格数量（比如你提到的20个空格），用REPLACE直接替换。
• 使用JSON_QUERY保留数组的JSON格式，避免被自动转义为字符串。

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