JSON Object node: SPLIT newbie question

Hi and apologies for what I am sure is a dumb newbie question.

I am trying to insert the daily carbon intensity records into a database table one record at a time as the size of the JSON string returned from the API call (HTTP Request) is too long to send to the SQL Server directly.

I cannot find any examples of how to use the SPLIT node to do this although I am sure it can.

I have included the flow export below and have created some dummy code in the SQL Server node as an illustration of what I am trying to achieve.

Can anyone help please?

[
    {
        "id": "60bb36e8.afff78",
        "type": "inject",
        "z": "25a64040.bdb25",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 140,
        "y": 60,
        "wires": [
            [
                "121ecda9.19be22"
            ]
        ]
    },
    {
        "id": "121ecda9.19be22",
        "type": "http request",
        "z": "25a64040.bdb25",
        "name": "Carbon Intensity",
        "method": "GET",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "https://api.carbonintensity.org.uk/intensity/date",
        "tls": "",
        "persist": false,
        "proxy": "",
        "authType": "",
        "x": 360,
        "y": 60,
        "wires": [
            [
                "9c49ecd6.0a88f",
                "7f8193d8.17c1ec"
            ]
        ]
    },
    {
        "id": "9c49ecd6.0a88f",
        "type": "debug",
        "z": "25a64040.bdb25",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 630,
        "y": 60,
        "wires": []
    },
    {
        "id": "7f8193d8.17c1ec",
        "type": "json",
        "z": "25a64040.bdb25",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 210,
        "y": 160,
        "wires": [
            [
                "be77bb00.cd17e8",
                "34c7c8bb.7ad948"
            ]
        ]
    },
    {
        "id": "be77bb00.cd17e8",
        "type": "debug",
        "z": "25a64040.bdb25",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 390,
        "y": 260,
        "wires": []
    },
    {
        "id": "34c7c8bb.7ad948",
        "type": "split",
        "z": "25a64040.bdb25",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "key",
        "x": 370,
        "y": 160,
        "wires": [
            [
                "d93e6d53.5f19f",
                "259d6295.38254e"
            ]
        ]
    },
    {
        "id": "d93e6d53.5f19f",
        "type": "debug",
        "z": "25a64040.bdb25",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 630,
        "y": 260,
        "wires": []
    },
    {
        "id": "212a6e99.85a372",
        "type": "debug",
        "z": "25a64040.bdb25",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 160,
        "wires": []
    },
    {
        "id": "259d6295.38254e",
        "type": "MSSQL",
        "z": "25a64040.bdb25",
        "mssqlCN": "de349d30.e337f",
        "name": "INSERT Carbon Intensity",
        "query": "-- DDL and sample data population, start\nDECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,\n        [from] DATETIMEOFFSET(3),\n        [to] DATETIMEOFFSET(3),\n        [forecast] int,\n        [actual] int,\n        [index] nvarchar(5)\n);\n\n-- DDL and sample data population, end\nDECLARE @data NVARCHAR(MAX) = {{{payload}}}\n    \n-- N'{\"data\": [{     \n--     \"from\": \"2021-09-23T23:00Z\",    \n--     \"to\": \"2021-09-23T23:30Z\",    \n--     \"intensity\": {      \n--         \"forecast\": 151,      \n--         \"actual\": 143,      \n--         \"index\": \"moderate\"    \n--         }  \n--     }]\n-- }';\n\n-- INSERT INTO @tbl ([from], [to], [forecast], [actual], [index])\n-- VALUES (\n--     @data.from, \n--     @data.to, \n--     @data.intentity.forecast, \n--     @data.intensity.actual, \n--     @data.intensity.index\n-- );\n\n-- test\nSELECT * FROM @tbl;",
        "outField": "payload",
        "x": 670,
        "y": 160,
        "wires": [
            [
                "212a6e99.85a372"
            ]
        ]
    },
    {
        "id": "de349d30.e337f",
        "type": "MSSQL-CN",
        "name": "AZURE SQL",
        "server": "andimcsqlserver.database.windows.net",
        "encyption": true,
        "database": "ANDIMC-SQL"
    }
]

Hello ..
your flow cannot be imported
please read the rules of the forum how to correctly post code / flows in the forum here
and then edit and re-post

Thanks for the heads up.

The Split node expects the data to be in msg.payload, whereas yours is in msg.payload.data. Insert a Change node between the JSON and Split node to move msg.payload.data into msg.payload.

image

I don't use MSSQL can't tell you what to do next. What format do you need the data in to go into that node?

@Colin Thank you so much, you have moved me onto the next section...