Dynamic JSON string coming from MQTT

Hello,

I have a flow where I am receiving data from MQTT, parsing it in a function block (JSON parsing) and then forming an insert statement out of it to feed it to a database. Attached the flow.

[
    {
        "id": "b190f6b08d42b688",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "2b72837f2767c939",
        "type": "MSSQL",
        "z": "b190f6b08d42b688",
        "mssqlCN": "14ebf81614eaa7be",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "msg",
        "paramsOpt": "",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [
            {
                "output": false,
                "name": "name",
                "type": "NVarChar(50)",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 700,
        "y": 300,
        "wires": [
            [
                "73860cd6ac08fd64"
            ]
        ]
    },
    {
        "id": "73860cd6ac08fd64",
        "type": "debug",
        "z": "b190f6b08d42b688",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 890,
        "y": 300,
        "wires": []
    },
    {
        "id": "79950c88eba525a3",
        "type": "inject",
        "z": "b190f6b08d42b688",
        "name": "SELECT",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "SELECT * FROM [DSDB].[dbo].[ATMEndProduct]",
        "payloadType": "str",
        "x": 480,
        "y": 300,
        "wires": [
            [
                "2b72837f2767c939"
            ]
        ]
    },
    {
        "id": "14582b8d5c908e2d",
        "type": "mqtt in",
        "z": "b190f6b08d42b688",
        "name": "MQTT_ATM",
        "topic": "/Krohne assemblage ATM flowmeter/production/product/removeProduct",
        "qos": "0",
        "datatype": "json",
        "broker": "7f92728b10ba1af4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 310,
        "y": 120,
        "wires": [
            [
                "856e1aebc9648b16",
                "650d0e7503ae5c8a"
            ]
        ]
    },
    {
        "id": "856e1aebc9648b16",
        "type": "debug",
        "z": "b190f6b08d42b688",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 510,
        "y": 120,
        "wires": []
    },
    {
        "id": "650d0e7503ae5c8a",
        "type": "function",
        "z": "b190f6b08d42b688",
        "name": "",
        "func": "try{\n    var product=msg.payload[\"->\"].product.value;\n    var articleCode=msg.payload[\"->\"].articleCode.value;\n    var revision=msg.payload[\"->\"].revision.value;\n    var recipe=msg.payload[\"->\"].recipe.value;\n    var recipeRevision=msg.payload[\"->\"].recipeRevision.value;\n    var serialNumber=msg.payload[\"->\"].serialNumber.value;\n    var orderNumber=msg.payload[\"->\"].orderNumber.value;\n    var labelCode=msg.payload[\"->\"].labelCode.value;\n    var readCode=msg.payload[\"->\"].readCode.value;\n    var status=msg.payload[\"->\"].status.value;\n    var solderStation=msg.payload[\"->\"].solderingStation.value;\n    var solderTime=msg.payload[\"->\"].solderingStation[\"->\"].solderTime.value;\n    var cooldownTime=msg.payload[\"->\"].solderingStation[\"->\"].cooldownTime.value;\n    var power=msg.payload[\"->\"].solderingStation[\"->\"].power.value;\n\n    var weld=msg.payload[\"->\"].weld.value;\n    var rotatorStartPosition=msg.payload[\"->\"].weld[\"->\"].rotatorStartPosition.value;\n    var rotatorDegToWeld=msg.payload[\"->\"].weld[\"->\"].rotatorDegToWeld.value;\n    var rotatorWeldSpeed=msg.payload[\"->\"].weld[\"->\"].rotatorWeldSpeed.value;\n    var laserPowerWatt=msg.payload[\"->\"].weld[\"->\"].laserPowerWatt.value;\n    var laserStartDelay=msg.payload[\"->\"].weld[\"->\"].laserStartDelay.value;\n    var clampPressure=msg.payload[\"->\"].weld[\"->\"].clampPressure.value;\n\n    var testResult=msg.payload[\"->\"].testResult.value;\n    var pressurize=msg.payload[\"->\"].pressurize.value;\n    var setPressure=msg.payload[\"->\"].pressurize[\"->\"].setPressure.value;\n    var allowedError=msg.payload[\"->\"].pressurize[\"->\"].allowedError.value;\n    var stabilisationTime=msg.payload[\"->\"].pressurize[\"->\"].stabilisationTime.value;\n    var isolationTime=msg.payload[\"->\"].pressurize[\"->\"].isolationTime.value;\n    var flowTime=msg.payload[\"->\"].pressurize[\"->\"].flowTime.value;\n    var maxTime=msg.payload[\"->\"].pressurize[\"->\"].maxTime.value;\n    var returnCode=msg.payload[\"->\"].pressurize[\"->\"].returnCode.value;\n    var isolatedPressure=msg.payload[\"->\"].pressurize[\"->\"].isolatedPressure.value;\n\n    var pressureDropTest=msg.payload[\"->\"].pressureDropTest.value;\n    var testTime=msg.payload[\"->\"].pressureDropTest[\"->\"].testTime.value;\n    var allowedStartError=msg.payload[\"->\"].pressureDropTest[\"->\"].allowedStartError.value;\n    var setTestPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].setTestPressure.value;\n    var allowedDrop=msg.payload[\"->\"].pressureDropTest[\"->\"].allowedDrop.value;\n    var returnCodePD=msg.payload[\"->\"].pressureDropTest[\"->\"].returnCode.value;\n    var startPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].startPressure.value;\n    var endPressure=msg.payload[\"->\"].pressureDropTest[\"->\"].endPressure.value;\n    var testResultPD=msg.payload[\"->\"].pressureDropTest[\"->\"].testResult.value;\n\n    var impedanceTest=msg.payload[\"->\"].impedanceTest.value;\n    var frequency=msg.payload[\"->\"].impedanceTest[\"->\"].frequencies.value;\n    var frequency1=frequency.substr(1,6)\n    var frequency2=frequency.substr(8,8)\n    var voltageLevel=msg.payload[\"->\"].impedanceTest[\"->\"].voltageLevel.value;\n    var minImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].minImpedances.value;\n    var minImpedance1=minImpedances.substr(1,8)\n    var minImpedance2=minImpedances.substr(10,5)\n    var maxImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].maxImpedances.value;\n    var maxImpedance1=maxImpedances.substr(1,9)\n    var maxImpedance2=maxImpedances.substr(11,10)\n    var minPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].minPhaseAngle.value;\n    var minPhaseAngle1=minPhaseAngle.substr(1,7)\n    var minPhaseAngle2=minPhaseAngle.substr(9,7)\n    var maxPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].maxPhaseAngle.value;\n    var maxPhaseAngle1=maxPhaseAngle.substr(1,5)\n    var maxPhaseAngle2=maxPhaseAngle.substr(7,5)\n    var requiredSerialNumber=msg.payload[\"->\"].impedanceTest[\"->\"].requiredSerialNumber.value;\n    var returnCodeIT=msg.payload[\"->\"].impedanceTest[\"->\"].returnCode.value;\n    var testResultIT=msg.payload[\"->\"].impedanceTest[\"->\"].testResult.value;\n    var leftImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].leftImpedances.value;\n    var leftImpedance1=leftImpedances.substr(1,9)\n    var leftImpedance2=leftImpedances.substr(11,8)\n    var leftPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].leftPhaseAngle.value;\n    var leftPhaseAngle1=leftPhaseAngle.substr(1,7)\n    var leftPhaseAngle2=leftPhaseAngle.substr(9,6)\n    var rightImpedances=msg.payload[\"->\"].impedanceTest[\"->\"].rightImpedances.value;\n    var rightImpedance1=rightImpedances.substr(1,9)\n    var rightImpedance2=rightImpedances.substr(11,8)\n    var rightPhaseAngle=msg.payload[\"->\"].impedanceTest[\"->\"].rightPhaseAngle.value;\n    var rightPhaseAngle1=rightPhaseAngle.substr(1,7)\n    var rightPhaseAngle2=rightPhaseAngle.substr(9,6)\n    var actualSerialNumber=msg.payload[\"->\"].impedanceTest[\"->\"].actualSerialNumber.value;\n\n    var zeroFlowTest=msg.payload[\"->\"].zeroFlowTest.value;\n    var numberOfSamples=msg.payload[\"->\"].zeroFlowTest[\"->\"].numberOfSamples.value;\n    var ticksPerSample=msg.payload[\"->\"].zeroFlowTest[\"->\"].ticksPerSample.value;\n    var dutSerialNumber=msg.payload[\"->\"].zeroFlowTest[\"->\"].dutSerialNumber.value;\n    var minDutStdDev=msg.payload[\"->\"].zeroFlowTest[\"->\"].minDutStdDev.value;\n    var maxDutStdDev=msg.payload[\"->\"].zeroFlowTest[\"->\"].maxDutStdDev.value;\n    var minDutAverage=msg.payload[\"->\"].zeroFlowTest[\"->\"].minDutAverage.value;\n    var maxDutAverage=msg.payload[\"->\"].zeroFlowTest[\"->\"].maxDutAverage.value;\n    var returnCodeZFT=msg.payload[\"->\"].zeroFlowTest[\"->\"].returnCode.value;\n    var testResultZFT=msg.payload[\"->\"].zeroFlowTest[\"->\"].testResult.value;\n    var dutSensorStatus=msg.payload[\"->\"].zeroFlowTest[\"->\"].dutSensorStatus.value;\n    var standardDeviationDut=msg.payload[\"->\"].zeroFlowTest[\"->\"].standardDeviationDut.value;\n    var runningAverageDut=msg.payload[\"->\"].zeroFlowTest[\"->\"].runningAverageDut.value;\n    var flowSource=msg.payload[\"->\"].zeroFlowTest[\"->\"].flowSource.value;\n    var samplesTaken=msg.payload[\"->\"].zeroFlowTest[\"->\"].samplesTaken.value;\n    var sampleTime=msg.payload[\"->\"].zeroFlowTest[\"->\"].sampleTime.value;\n    var refSensorStatus=msg.payload[\"->\"].zeroFlowTest[\"->\"].refSensorStatus.value;\n\n    var setFlow=msg.payload[\"->\"].setFlow.value;\n    var stabilisationTimeSF=msg.payload[\"->\"].setFlow[\"->\"].stabilisationTime.value;\n\n    var flowTest=msg.payload[\"->\"].flowTest.value;\n    var numberOfSamples1=msg.payload[\"->\"].flowTest[\"->\"].numberOfSamples.value;\n    var ticksPerSample1=msg.payload[\"->\"].flowTest[\"->\"].ticksPerSample.value;\n    var dutSerialNumber1=msg.payload[\"->\"].flowTest[\"->\"].dutSerialNumber.value;\n    var refSerialNumber1=msg.payload[\"->\"].flowTest[\"->\"].refSerialNumber.value;\n    var minDutAverageDeviation1=msg.payload[\"->\"].flowTest[\"->\"].minDutAverageDeviation.value;\n    var maxDutAverageDeviation1=msg.payload[\"->\"].flowTest[\"->\"].maxDutAverageDeviation.value;\n    var minRefAverage1=msg.payload[\"->\"].flowTest[\"->\"].minRefAverage.value;\n    var maxRefAverage1=msg.payload[\"->\"].flowTest[\"->\"].maxRefAverage.value;\n    var minDutStdDev1=msg.payload[\"->\"].flowTest[\"->\"].minDutStdDev.value;\n    var maxDutStdDev1=msg.payload[\"->\"].flowTest[\"->\"].maxDutStdDev.value;\n    var minRefStdDev1=msg.payload[\"->\"].flowTest[\"->\"].minRefStdDev.value;\n    var maxRefStdDev1=msg.payload[\"->\"].flowTest[\"->\"].maxRefStdDev.value;\n    var minDutTemperature1=msg.payload[\"->\"].flowTest[\"->\"].minDutTemperature.value;\n    var maxDutTemperature1=msg.payload[\"->\"].flowTest[\"->\"].maxDutTemperature.value;\n    var sizeFactor1=msg.payload[\"->\"].flowTest[\"->\"].sizeFactor.value;\n    var returnCodeFT1=msg.payload[\"->\"].flowTest[\"->\"].returnCode.value;\n    var testResultFT1=msg.payload[\"->\"].flowTest[\"->\"].testResult.value;\n    var standardDeviationDut1=msg.payload[\"->\"].flowTest[\"->\"].standardDeviationDut.value;\n    var standardDeviationRef1=msg.payload[\"->\"].flowTest[\"->\"].standardDeviationRef.value;\n    var temperatureDut1=msg.payload[\"->\"].flowTest[\"->\"].temperatureDut.value;\n    var temperatureRef1=msg.payload[\"->\"].flowTest[\"->\"].temperatureRef.value;\n    var runningAverageDut1=msg.payload[\"->\"].flowTest[\"->\"].runningAverageDut.value;\n    var runningAverageRef1=msg.payload[\"->\"].flowTest[\"->\"].runningAverageRef.value;\n    var flowSource1=msg.payload[\"->\"].flowTest[\"->\"].flowSource.value;\n    var samplesTaken1=msg.payload[\"->\"].flowTest[\"->\"].samplesTaken.value;\n    var sampleTime1=msg.payload[\"->\"].flowTest[\"->\"].sampleTime.value;\n\n    var prevSerialNumber=msg.payload[\"->\"].prevSerialNumber.value;\n    var dataTimeStamp=msg.payload[\"->\"].dataTimeStamp.value;\n\n    var productC=msg.payload[\"/\"].coil[\"->\"].product.value;\n    var articleCodeC=msg.payload[\"/\"].coil[\"->\"].articleCode.value;\n    var revisionC=msg.payload[\"/\"].coil[\"->\"].revision.value;\n    var statusC=msg.payload[\"/\"].coil[\"->\"].status.value;\n    var readCodeC=msg.payload[\"/\"].coil[\"->\"].readCode.value;\n    var measuredTrayPosition=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition.value;\n    //var measuredTrayPosition1=measuredTrayPosition.substr(1,9)\n    //var measuredTrayPosition2=measuredTrayPosition.substr(11,8)\n    var x=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].x.value;\n    var y=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].y.value;\n    var z=msg.payload[\"/\"].coil[\"->\"].measuredTrayPosition[\"->\"].z.value;\n    var trayDeltaC=msg.payload[\"/\"].coil[\"->\"].trayDelta.value;\n    var x1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].x.value;\n    var y1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].y.value;\n    var z1=msg.payload[\"/\"].coil[\"->\"].trayDelta[\"->\"].rz.value;\n    var productUB=msg.payload[\"/\"].coil[\"/\"].uBracket[\"->\"].product.value;\n    var statusUB=msg.payload[\"/\"].coil[\"/\"].uBracket[\"->\"].status.value;\n\n    var productEC=msg.payload[\"/\"].eCable[\"->\"].product.value;\n    var statusEC=msg.payload[\"/\"].eCable[\"->\"].status.value;\n    var numberEC=msg.payload[\"/\"].eCable[\"->\"].number.value;\n\n    var articleCodeT=msg.payload[\"/\"].tube[\"->\"].articleCode.value;\n    var statusT=msg.payload[\"/\"].tube[\"->\"].status.value;\n    var productT=msg.payload[\"/\"].tube[\"->\"].product.value;\n    var trayCodeT=msg.payload[\"/\"].tube[\"->\"].trayCode.value;\n    var cmdDispense1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1.value;\n    var dispenseTime1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1[\"->\"].dispenseTime.value;\n    var pressureSetPoint1=msg.payload[\"/\"].tube[\"->\"].cmdDispense1[\"->\"].pressureSetPoint.value;\n    var cmdDispense2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2.value;\n    var dispenseTime2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2[\"->\"].dispenseTime.value;\n    var pressureSetPoint2=msg.payload[\"/\"].tube[\"->\"].cmdDispense2[\"->\"].pressureSetPoint.value;\n\n    var productG=msg.payload[\"/\"].tube[\"/\"].Grease[\"->\"].product.value;\n    var statusG=msg.payload[\"/\"].tube[\"/\"].Grease[\"->\"].status.value;\n\n    var productCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].product.value;\n    var articleCodeCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].articleCode.value;\n    var revisionCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].revision.value;\n    var statusCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].status.value;\n    var airPressureCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].airPressure.value;\n    var airBackPressureCup=msg.payload[\"/\"].tube[\"/\"].cup[\"->\"].airBackPressure.value;\n\n    var productOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].product.value;\n    var articleCodeOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].articleCode.value;\n    var revisionOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].revision.value;\n    var statusOR=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].status.value;\n    var minGreyValue=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].minGreyValue.value;\n    var maxGreyValue=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].maxGreyValue.value;\n    var avgGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].avgGreyScale.value;\n    var lowestGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].lowestGreyScale.value;\n    var highestGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].highestGreyScale.value;\n    var stdDevGreyScale=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].stdDevGreyScale.value;\n    var batchCode=msg.payload[\"/\"].tube[\"/\"].cup[\"/\"].oRing[\"->\"].batchCode.value;\n}\ncatch(error) {\n    //nothing\n}\n\nvar msg1={};\nvar stringy=\"INSERT INTO [DSDB].[dbo].[ATMRemoveProduct] ([product] ,[articleCode] ,[revision] ,[recipe] ,[recipeRevision] ,[serialNumber] ,[orderNumber] ,[labelCode] ,[readCode] ,[status] ,[solderingStation] ,[solderTime] ,[cooldownTime] ,[power] ,[weld] ,[rotatorStartPosition] ,[rotatorDegToWeld] ,[rotatorWeldSpeed] ,[laserPowerWatt] ,[laserStartDelay] ,[clampPressure] ,[testResult] ,[pressurize] ,[setPressure] ,[allowedError] ,[stabilisationTime] ,[isolationTime] ,[flowTime] ,[maxTime] ,[returnCodeP] ,[isolatedPressure] ,[pressureDropTest] ,[testTime] ,[allowedStartError] ,[setTestPressure] ,[allowedDrop] ,[returnCodePD] ,[startPressure] ,[endPressure] ,[testResultPD] ,[impedanceTest] ,[frequency1] ,[frequency2] ,[voltageLevel] ,[minImpedance1] ,[minImpedance2] ,[maxImpedance1] ,[maxImpedance2] ,[minPhaseAngle1] ,[minPhaseAngle2] ,[maxPhaseAngle1] ,[maxPhaseAngle2] ,[requiredSerialNumber] ,[returnCodeIT] ,[testResultIT] ,[leftImpedance1] ,[leftImpedance2] ,[leftPhaseAngle1] ,[leftPhaseAngle2] ,[rightImpedance1] ,[rightImpedance2] ,[rightPhaseAngle1] ,[rightPhaseAngle2] ,[actualSerialNumber] ,[zeroFlowTest] ,[numberOfSamples] ,[ticksPerSample] ,[dutSerialNumber] ,[minDutStdDev] ,[maxDutStdDev] ,[minDutAverage] ,[maxDutAverage] ,[returnCodeZFT] ,[testResultZFT] ,[dutSensorStatus] ,[standardDeviationDut] ,[runningAverageDut] ,[flowSource] ,[samplesTaken] ,[sampleTime] ,[refSensorStatus] ,[setFlow] ,[stabilitsationTimeSF] ,[flowTest] ,[numberOfSamples1] ,[ticksPerSample1] ,[dutSerialNumber1] ,[refSerialNumber1] ,[minDutAverageDeviation1] ,[maxDutAverageDeviation1] ,[minRefAverage1] ,[maxRefAverage] ,[minDutStdDev1] ,[maxDutStdDev1] ,[minRefStdDev1] ,[maxRefStdDev1] ,[maxDutTemperature1] ,[minDutTemperature1] ,[sizeFactor1] ,[returnCodeFT1] ,[testResultFT1] ,[standardDeviationDut1] ,[standardDeviationRef1] ,[temperatureDut1] ,[temperatureRef1] ,[runningAverageDut1] ,[runningAverageRef1] ,[sampleTime1] ,[flowSource1] ,[samplesTaken1] ,[prevSerialNumber] ,[dataTimeStamp] ,[productC] ,[articleCodeC] ,[revisionC] ,[statusC] ,[readCodeC] ,[measuredTrayPosition1] ,[x] ,[y] ,[z] ,[trayDeltaC] ,[x1] ,[y1] ,[z1] ,[productUB] ,[statusUB] ,[productEC] ,[statusEC] ,[numberEC] ,[articleCodeT] ,[statusT] ,[productT] ,[trayCodeT] ,[cmdDispense1] ,[dispenseTime1] ,[pressureSetPoint1] ,[cmdDispense2] ,[dispenseTime2] ,[pressureSetPoint2] ,[productG] ,[statusG] ,[productCup] ,[articleCodeCup] ,[revisionCup] ,[statusCup] ,[airPressureCup] ,[airBackPressureCup] ,[productOR] ,[articleCodeOR] ,[revisionOR] ,[statusOR] ,[minGreyValue] ,[maxGreyValue] ,[avgGreyScale] ,[lowestGreyScale] ,[highestGreyScale] ,[stdDevGreyScale] ,[batchCode]) VALUES ('\"+product+\"','\"+articleCode+\"','\"+revision+\"','\"+recipe+\"','\"+recipeRevision+\"','\"+serialNumber+\"','\"+orderNumber+\"','\"+labelCode+\"','\"+readCode+\"','\"+status+\"','\"+solderStation+\"','\"+solderTime+\"','\"+cooldownTime+\"','\"+power+\"','\"+weld+\"','\"+rotatorStartPosition+\"','\"+rotatorDegToWeld+\"','\"+rotatorWeldSpeed+\"','\"+laserPowerWatt+\"','\"+laserStartDelay+\"','\"+clampPressure+\"','\"+testResult+\"','\"+pressurize+\"','\"+setPressure+\"','\"+allowedError+\"','\"+stabilisationTime+\"','\"+isolationTime+\"','\"+flowTime+\"','\"+maxTime+\"','\"+returnCode+\"','\"+isolatedPressure+\"','\"+pressureDropTest+\"','\"+testTime+\"','\"+allowedStartError+\"','\"+setTestPressure+\"','\"+allowedDrop+\"','\"+returnCodePD+\"','\"+startPressure+\"','\"+endPressure+\"','\"+testResultPD+\"','\"+impedanceTest+\"','\"+frequency1+\"','\"+frequency2+\"','\"+voltageLevel+\"','\"+minImpedance1+\"','\"+minImpedance2+\"','\"+maxImpedance1+\"','\"+maxImpedance2+\"','\"+minPhaseAngle1+\"','\"+minPhaseAngle2+\"','\"+maxPhaseAngle1+\"','\"+maxPhaseAngle2+\"','\"+requiredSerialNumber+\"','\"+returnCodeIT+\"','\"+testResultIT+\"','\"+leftImpedance1+\"','\"+leftImpedance2+\"','\"+leftPhaseAngle1+\"','\"+leftPhaseAngle2+\"','\"+rightImpedance1+\"','\"+rightImpedance2+\"','\"+rightPhaseAngle1+\"','\"+rightPhaseAngle2+\"','\"+actualSerialNumber+\"','\"+zeroFlowTest+\"','\"+numberOfSamples+\"','\"+ticksPerSample+\"','\"+dutSerialNumber+\"','\"+minDutStdDev+\"','\"+maxDutStdDev+\"','\"+minDutAverage+\"','\"+maxDutAverage+\"','\"+returnCodeZFT+\"','\"+testResultZFT+\"','\"+dutSensorStatus+\"','\"+standardDeviationDut+\"','\"+runningAverageDut+\"','\"+flowSource+\"','\"+samplesTaken+\"','\"+sampleTime+\"','\"+refSensorStatus+\"','\"+setFlow+\"','\"+stabilisationTimeSF+\"','\"+flowTest+\"','\"+numberOfSamples1+\"','\"+ticksPerSample1+\"','\"+dutSerialNumber1+\"','\"+refSerialNumber1+\"','\"+minDutAverageDeviation1+\"','\"+maxDutAverageDeviation1+\"','\"+minRefAverage1+\"','\"+maxRefAverage1+\"','\"+minDutStdDev1+\"','\"+maxDutStdDev1+\"','\"+minRefStdDev1+\"','\"+maxRefStdDev1+\"','\"+minDutTemperature1+\"','\"+maxDutTemperature1+\"','\"+sizeFactor1+\"','\"+returnCodeFT1+\"','\"+testResultFT1+\"','\"+standardDeviationDut1+\"','\"+standardDeviationRef1+\"','\"+temperatureDut1+\"','\"+temperatureRef1+\"','\"+runningAverageDut1+\"','\"+runningAverageRef1+\"','\"+flowSource1+\"','\"+samplesTaken1+\"','\"+sampleTime1+\"','\"+prevSerialNumber+\"','\"+dataTimeStamp+\"','\"+productC+\"','\"+articleCodeC+\"','\"+revisionC+\"','\"+statusC+\"','\"+readCodeC+\"','\"+measuredTrayPosition+\"','\"+x+\"','\"+y+\"','\"+z+\"','\"+trayDeltaC+\"','\"+x1+\"','\"+y1+\"','\"+z1+\"','\"+productUB+\"','\"+statusUB+\"','\"+productEC+\"','\"+statusEC+\"','\"+numberEC+\"','\"+articleCodeT+\"','\"+statusT+\"','\"+productT+\"','\"+trayCodeT+\"','\"+cmdDispense1+\"','\"+dispenseTime1+\"','\"+pressureSetPoint1+\"','\"+cmdDispense2+\"','\"+dispenseTime2+\"','\"+pressureSetPoint2+\"','\"+productG+\"','\"+statusG+\"','\"+productCup+\"','\"+articleCodeCup+\"','\"+revisionCup+\"','\"+statusCup+\"','\"+airPressureCup+\"','\"+airBackPressureCup+\"','\"+productOR+\"','\"+articleCodeOR+\"','\"+revisionOR+\"','\"+statusOR+\"','\"+minGreyValue+\"','\"+maxGreyValue+\"','\"+avgGreyScale+\"','\"+lowestGreyScale+\"','\"+highestGreyScale+\"','\"+stdDevGreyScale+\"','\"+batchCode+\"')\";\nmsg1.payload=stringy;\nreturn msg1;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 520,
        "y": 200,
        "wires": [
            [
                "9cd3cc5264450047",
                "2b72837f2767c939"
            ]
        ]
    },
    {
        "id": "9cd3cc5264450047",
        "type": "debug",
        "z": "b190f6b08d42b688",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 690,
        "y": 200,
        "wires": []
    },
    {
        "id": "14ebf81614eaa7be",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "GauravTest",
        "server": "10.51.1.16",
        "port": "1433",
        "encyption": false,
        "trustServerCertificate": true,
        "database": "DSDB",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "100",
        "parseJSON": true,
        "enableArithAbort": true
    },
    {
        "id": "7f92728b10ba1af4",
        "type": "mqtt-broker",
        "name": "ATM Machine",
        "broker": "10.51.171.10",
        "port": "1883",
        "clientid": "node-red",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "sessionExpiry": ""
    }
]

The problem I have is that the JSON that I receive is not the same everytime. I have a standard format. But sometimes it is incomplete. In such cases, while parsing the JSON, I get an error saying that the value cannot be read (obviously because it was not received). But I would like to form an insert statement leaving the values which I dont receive as blank.

I tried to do this with a try and catch statement. I thought it worked. But try catch stops with the first unavailable value while parsing the json.

Can someone suggest a way around this ? Or a better way of doing this ? Attached also a complete format of the JSON. In case of an incomplete JSON, leave some values out.

{"->":{"product":{"value":"ATM"},"articleCode":{"value":"4006110801"},"revision":{"value":"003"},"recipe":{"value":"4006110801 ATM DN25 FKM"},"recipeRevision":{"value":"001"},"serialNumber":{"value":"22814740"},"orderNumber":{"value":"dn25fkm 4-10-2022"},"labelCode":{"value":"4006110801_003_22814740_X"},"readCode":{"value":"4006110801_003_22814740_X"},"status":{"value":20},"solderingStation":{"value":"0093561","->":{"solderTime":{"value":4000},"cooldownTime":{"value":2000},"power":{"value":99.000000}}},"weld":{"value":925.000000,"->":{"rotatorStartPosition":{"value":4.000000},"rotatorDegToWeld":{"value":-7.000000},"rotatorWeldSpeed":{"value":31.800000},"laserPowerWatt":{"value":600.000000},"laserStartDelay":{"value":10},"clampPressure":{"value":2.500000}}},"testResult":{"value":2},"pressurize":{"value":"4006110801_003_22814738_X","->":{"setPressure":{"value":24.000000},"allowedError":{"value":5.000000},"stabilisationTime":{"value":1000},"isolationTime":{"value":15000},"flowTime":{"value":10000},"maxTime":{"value":5000},"returnCode":{"value":2},"isolatedPressure":{"value":24.861416}}},"pressureDropTest":{"value":"22814736","->":{"testTime":{"value":30000},"allowedStartError":{"value":5.000000},"setTestPressure":{"value":24.000000},"allowedDrop":{"value":2.500000},"returnCode":{"value":2},"startPressure":{"value":24.860900},"endPressure":{"value":24.572014},"testResult":{"value":2}}},"impedanceTest":{"value":0.385815,"->":{"frequencies":{"value":"[20.000,2000.000]"},"voltageLevel":{"value":0.100000},"minImpedances":{"value":"[3000.000,0.000]"},"maxImpedances":{"value":"[25000.000,100000.000]"},"minPhaseAngle":{"value":"[-90.000,-15.000]"},"maxPhaseAngle":{"value":"[0.000,0.000]"},"requiredSerialNumber":{"value":""},"returnCode":{"value":2},"testResult":{"value":2},"leftImpedances":{"value":"[19013.599,5855.769]"},"leftPhaseAngle":{"value":"[-47.437,-7.655]"},"rightImpedances":{"value":"[20653.100,6082.069]"},"rightPhaseAngle":{"value":"[-46.358,-8.447]"},"actualSerialNumber":{"value":"GER880566"}}},"zeroFlowTest":{"value":0.350000,"->":{"numberOfSamples":{"value":50.000000},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"minDutStdDev":{"value":0.000000},"maxDutStdDev":{"value":0.008000},"minDutAverage":{"value":-0.002000},"maxDutAverage":{"value":0.002000},"returnCode":{"value":2},"testResult":{"value":2},"dutSensorStatus":{"value":100512},"standardDeviationDut":{"value":0.001696},"runningAverageDut":{"value":-0.000081},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50},"sampleTime":{"value":12860},"refSensorStatus":{"value":0}}},"setFlow":{"value":24.000000,"->":{"stabilisationTime":{"value":8000}}},"flowTest":{"value":30000,"->":{"numberOfSamples":{"value":50.000000},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"refSerialNumber":{"value":"20180612_30"},"minDutAverageDeviation":{"value":-15.000000},"maxDutAverageDeviation":{"value":10.000000},"minRefAverage":{"value":0.350000},"maxRefAverage":{"value":0.450000},"minDutStdDev":{"value":0.000100},"maxDutStdDev":{"value":0.020000},"minRefStdDev":{"value":0.000100},"maxRefStdDev":{"value":0.010000},"maxDutTemperature":{"value":999999999999.900024},"minDutTemperature":{"value":-999999999999.900024},"sizeFactor":{"value":4.468500},"returnCode":{"value":2},"testResult":{"value":2},"standardDeviationDut":{"value":0.006166},"standardDeviationRef":{"value":0.002666},"temperatureDut":{"value":-55.000000},"temperatureRef":{"value":26.591309},"runningAverageDut":{"value":0.384683},"runningAverageRef":{"value":0.416008},"sampleTime":{"value":16290},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50}}},"prevSerialNumber":{"value":"22814739"},"dataTimeStamp":{"value":"2022-10-04T08:16:57.043Z"}},"/":{"coil":{"->":{"product":{"value":"Coil"},"articleCode":{"value":"4008043701"},"revision":{"value":"000"},"status":{"value":4},"readCode":{"value":"4008043701 000 2108 23691 63@ 1600"},"measuredTrayPosition":{"value":36.500000,"->":{"x":{"value":252.000000},"y":{"value":36.500000},"z":{"value":55.000000}}},"trayDelta":{"value":"000","->":{"x":{"value":1.637711},"y":{"value":-1.864510},"rz":{"value":2.076050}}}},"/":{"uBracket":{"->":{"product":{"value":"Ubracket"},"status":{"value":1}}}}},"eCable":{"->":{"product":{"value":"Ecable"},"status":{"value":2},"number":{"value":13}}},"tube":{"->":{"articleCode":{"value":"4005945302"},"status":{"value":6},"product":{"value":"Tube"},"trayCode":{"value":"04379 4005945302 003"},"rawAlignPosition":{"value":940.000000},"maxAlignPosition":{"value":950.000000},"minAlignPosition":{"value":925.000000},"rawPressPosition":{"value":967.000000},"maxPressPosition":{"value":970.000000},"minPressPosition":{"value":960.000000},"cmdDispense1":{"value":2,"->":{"dispenseTime":{"value":50},"pressureSetPoint":{"value":4.100000}}},"cmdDispense2":{"value":1000,"->":{"dispenseTime":{"value":45},"pressureSetPoint":{"value":4.100000}}}},"/":{"Grease":{"->":{"product":{"value":"Grease"},"status":{"value":1}}},"cup":{"->":{"product":{"value":"Temperature insert"},"articleCode":{"value":""},"revision":{"value":""},"status":{"value":2},"airPressure":{"value":5.000000},"airBackPressure":{"value":2.500000}},"/":{"oRing":{"->":{"product":{"value":"oRing"},"articleCode":{"value":"060018V8836S190"},"revision":{"value":"000"},"status":{"value":2},"minGreyValue":{"value":90},"maxGreyValue":{"value":225},"avgGreyScale":{"value":135},"lowestGreyScale":{"value":92},"highestGreyScale":{"value":218},"stdDevGreyScale":{"value":1518},"batchCode":{"value":"0093561"}}}}}}}}}

Thanks in advance!

You could apply a default to each var

  let product = msg.payload["->"].product.value || "";

So if value is undefined the default would be an empty string

Wow, that is a LOT of variables.

The better way is to use parameters but in this case, there are so many variables it would be VERY laborious to enter them all in the parameters section of the MSSQL-PLUS node.

Better still would be to use something like postgres or mongo (or any other document style database) and simply store the object as-is instead of having a totally un-managable table of 180+ fields!

Any how, you are where you are so my suggestion to get you past this hurdle would be to use optional chaining (requires NODEJS v14+) e.g...

const product = msg.payload["->"]?.product?.value;
const articleCode = msg.payload["->"]?.articleCode?.value;
const revision = msg.payload["->"]?.revision?.value;
const recipe = msg.payload["->"]?.recipe?.value;
  • If msg.payload["->"] is null/undefined, product will be undefined (wont error)
  • If msg.payload["->"].product is null/undefined, product will be undefined (wont error)
  • If msg.payload["->"].product is OK, product will be set to msg.payload["->"]?.product?.value (even if it is null or undefined)

Alternatively, you could have a fall back value...

const product = msg.payload["->"]?.product?.value || 'missing';
const articleCode = msg.payload["->"]?.articleCode?.value || 'missing';
const revision = msg.payload["->"]?.revision?.value || 'missing';
const recipe = msg.payload["->"]?.recipe?.value || 'missing';
  • If msg.payload["->"] is null/undefined, product will be missing (wont error)
  • If msg.payload["->"].product is null/undefined, product will be missing (wont error)
  • If msg.payload["->"].product.value is null/undefined, product will be set to missing (wont error)
  • If msg.payload["->"].product.value is "something", product will be set to msg.payload["->"].product.value

In an ideal world - yes, however an error will be thrown when payload doesnt contain ["->"].product

The problem I have is that the JSON that I receive is not the same everytime.

This is why document databases were invented, but as @Steve-Mcl stated, you can insert the object as is, in most databases nowadays, not sure if mssql is the best candidate, but it should be able to handle it.

I am experimenting with surrealdb and there it is a total breeze (and so is selecting/returning the data).

1 Like

This solves my problem mostly. On some incomplete messages it works fine. But for some it throws an error.

[
    {
        "id": "9eb09f2c99f04cac",
        "type": "tab",
        "label": "Flow 3",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "6529a2c4fbd46724",
        "type": "MSSQL",
        "z": "9eb09f2c99f04cac",
        "mssqlCN": "14ebf81614eaa7be",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "msg",
        "paramsOpt": "",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [
            {
                "output": false,
                "name": "name",
                "type": "NVarChar(50)",
                "valueType": "msg",
                "value": "payload",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 740,
        "y": 400,
        "wires": [
            [
                "5ddbb109bc75bf71"
            ]
        ]
    },
    {
        "id": "5ddbb109bc75bf71",
        "type": "debug",
        "z": "9eb09f2c99f04cac",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 930,
        "y": 400,
        "wires": []
    },
    {
        "id": "fcf0c47d0d98deab",
        "type": "inject",
        "z": "9eb09f2c99f04cac",
        "name": "SELECT",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "SELECT * FROM [DSDB].[dbo].[ATMEndProduct]",
        "payloadType": "str",
        "x": 520,
        "y": 400,
        "wires": [
            [
                "6529a2c4fbd46724"
            ]
        ]
    },
    {
        "id": "a2c3a5300382fe0f",
        "type": "mqtt in",
        "z": "9eb09f2c99f04cac",
        "name": "MQTT_ATM",
        "topic": "/Krohne assemblage ATM flowmeter/production/product/endProduct",
        "qos": "0",
        "datatype": "json",
        "broker": "7f92728b10ba1af4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 350,
        "y": 220,
        "wires": [
            [
                "d7ce07b5165986fe",
                "9a6094d969c93867"
            ]
        ]
    },
    {
        "id": "d7ce07b5165986fe",
        "type": "debug",
        "z": "9eb09f2c99f04cac",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 550,
        "y": 220,
        "wires": []
    },
    {
        "id": "9a6094d969c93867",
        "type": "function",
        "z": "9eb09f2c99f04cac",
        "name": "",
        "func": "    const product=msg.payload[\"->\"]?.product?.value;\n    const articleCode=msg.payload[\"->\"]?.articleCode?.value;\n    const revision=msg.payload[\"->\"]?.revision?.value;\n    const recipe=msg.payload[\"->\"]?.recipe?.value;\n    const recipeRevision=msg.payload[\"->\"]?.recipeRevision?.value;\n    const serialNumber=msg.payload[\"->\"]?.serialNumber?.value;\n    const orderNumber=msg.payload[\"->\"]?.orderNumber?.value;\n    const labelCode=msg.payload[\"->\"]?.labelCode?.value;\n    const readCode=msg.payload[\"->\"]?.readCode?.value;\n    const status=msg.payload[\"->\"]?.status?.value;\n    const solderStation=msg.payload[\"->\"]?.solderingStation?.value;\n    const solderTime=msg.payload[\"->\"]?.solderingStation[\"->\"]?.solderTime?.value;\n    const cooldownTime=msg.payload[\"->\"]?.solderingStation[\"->\"]?.cooldownTime?.value;\n    const power=msg.payload[\"->\"]?.solderingStation[\"->\"]?.power?.value;\n\n    const weld=msg.payload[\"->\"]?.weld?.value;\n    const rotatorStartPosition=msg.payload[\"->\"]?.weld[\"->\"]?.rotatorStartPosition?.value;\n    const rotatorDegToWeld=msg.payload[\"->\"]?.weld[\"->\"]?.rotatorDegToWeld?.value;\n    const rotatorWeldSpeed=msg.payload[\"->\"]?.weld[\"->\"]?.rotatorWeldSpeed?.value;\n    const laserPowerWatt=msg.payload[\"->\"]?.weld[\"->\"]?.laserPowerWatt?.value;\n    const laserStartDelay=msg.payload[\"->\"]?.weld[\"->\"]?.laserStartDelay?.value;\n    const clampPressure=msg.payload[\"->\"]?.weld[\"->\"]?.clampPressure?.value;\n\n    const testResult=msg.payload[\"->\"]?.testResult?.value;\n    const pressurize=msg.payload[\"->\"]?.pressurize?.value;\n    const setPressure=msg.payload[\"->\"]?.pressurize[\"->\"]?.setPressure?.value;\n    const allowedError=msg.payload[\"->\"]?.pressurize[\"->\"]?.allowedError?.value;\n    const stabilisationTime=msg.payload[\"->\"]?.pressurize[\"->\"]?.stabilisationTime?.value;\n    const isolationTime=msg.payload[\"->\"]?.pressurize[\"->\"]?.isolationTime?.value;\n    const flowTime=msg.payload[\"->\"]?.pressurize[\"->\"]?.flowTime?.value;\n    const maxTime=msg.payload[\"->\"]?.pressurize[\"->\"]?.maxTime?.value;\n    const returnCode=msg.payload[\"->\"]?.pressurize[\"->\"]?.returnCode?.value;\n    const isolatedPressure=msg.payload[\"->\"]?.pressurize[\"->\"]?.isolatedPressure?.value;\n\n    const pressureDropTest=msg.payload[\"->\"]?.pressureDropTest?.value;\n    const testTime=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.testTime?.value;\n    const allowedStartError=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.allowedStartError?.value;\n    const setTestPressure=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.setTestPressure?.value;\n    const allowedDrop=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.allowedDrop?.value;\n    const returnCodePD=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.returnCode?.value;\n    const startPressure=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.startPressure?.value;\n    const endPressure=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.endPressure?.value;\n    const testResultPD=msg.payload[\"->\"]?.pressureDropTest[\"->\"]?.testResult?.value;\n\n    const impedanceTest=msg.payload[\"->\"]?.impedanceTest?.value;\n    const frequency=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.frequencies?.value;\n    const frequency1=frequency.substr(1,6)\n    const frequency2=frequency.substr(8,8)\n    const voltageLevel=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.voltageLevel?.value;\n    const minImpedances=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.minImpedances?.value;\n    const minImpedance1=minImpedances.substr(1,8)\n    const minImpedance2=minImpedances.substr(10,5)\n    const maxImpedances=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.maxImpedances?.value;\n    const maxImpedance1=maxImpedances.substr(1,9)\n    const maxImpedance2=maxImpedances.substr(11,10)\n    const minPhaseAngle=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.minPhaseAngle?.value;\n    const minPhaseAngle1=minPhaseAngle.substr(1,7)\n    const minPhaseAngle2=minPhaseAngle.substr(9,7)\n    const maxPhaseAngle=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.maxPhaseAngle?.value;\n    const maxPhaseAngle1=maxPhaseAngle.substr(1,5)\n    const maxPhaseAngle2=maxPhaseAngle.substr(7,5)\n    const requiredSerialNumber=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.requiredSerialNumber?.value;\n    const returnCodeIT=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.returnCode?.value;\n    const testResultIT=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.testResult?.value;\n    const leftImpedances=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.leftImpedances?.value;\n    const leftImpedance1=leftImpedances.substr(1,9)\n    const leftImpedance2=leftImpedances.substr(11,8)\n    const leftPhaseAngle=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.leftPhaseAngle?.value;\n    const leftPhaseAngle1=leftPhaseAngle.substr(1,7)\n    const leftPhaseAngle2=leftPhaseAngle.substr(9,6)\n    const rightImpedances=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.rightImpedances?.value;\n    const rightImpedance1=rightImpedances.substr(1,9)\n    const rightImpedance2=rightImpedances.substr(11,8)\n    const rightPhaseAngle=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.rightPhaseAngle?.value;\n    const rightPhaseAngle1=rightPhaseAngle.substr(1,7)\n    const rightPhaseAngle2=rightPhaseAngle.substr(9,6)\n    const actualSerialNumber=msg.payload[\"->\"]?.impedanceTest[\"->\"]?.actualSerialNumber?.value;\n\n    const zeroFlowTest=msg.payload[\"->\"]?.zeroFlowTest?.value;\n    const numberOfSamples=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.numberOfSamples?.value;\n    const ticksPerSample=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.ticksPerSample?.value;\n    const dutSerialNumber=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.dutSerialNumber?.value;\n    const minDutStdDev=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.minDutStdDev?.value;\n    const maxDutStdDev=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.maxDutStdDev?.value;\n    const minDutAverage=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.minDutAverage?.value;\n    const maxDutAverage=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.maxDutAverage?.value;\n    const returnCodeZFT=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.returnCode?.value;\n    const testResultZFT=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.testResult?.value;\n    const dutSensorStatus=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.dutSensorStatus?.value;\n    const standardDeviationDut=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.standardDeviationDut?.value;\n    const runningAverageDut=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.runningAverageDut?.value;\n    const flowSource=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.flowSource?.value;\n    const samplesTaken=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.samplesTaken?.value;\n    const sampleTime=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.sampleTime?.value;\n    const refSensorStatus=msg.payload[\"->\"]?.zeroFlowTest[\"->\"]?.refSensorStatus?.value;\n\n    const setFlow=msg.payload[\"->\"]?.setFlow?.value;\n    const stabilisationTimeSF=msg.payload[\"->\"]?.setFlow[\"->\"]?.stabilisationTime?.value;\n\n    const flowTest=msg.payload[\"->\"]?.flowTest?.value;\n    const numberOfSamples1=msg.payload[\"->\"]?.flowTest[\"->\"]?.numberOfSamples?.value;\n    const ticksPerSample1=msg.payload[\"->\"]?.flowTest[\"->\"]?.ticksPerSample?.value;\n    const dutSerialNumber1=msg.payload[\"->\"]?.flowTest[\"->\"]?.dutSerialNumber?.value;\n    const refSerialNumber1=msg.payload[\"->\"]?.flowTest[\"->\"]?.refSerialNumber?.value;\n    const minDutAverageDeviation1=msg.payload[\"->\"]?.flowTest[\"->\"]?.minDutAverageDeviation?.value;\n    const maxDutAverageDeviation1=msg.payload[\"->\"]?.flowTest[\"->\"]?.maxDutAverageDeviation?.value;\n    const minRefAverage1=msg.payload[\"->\"]?.flowTest[\"->\"]?.minRefAverage?.value;\n    const maxRefAverage1=msg.payload[\"->\"]?.flowTest[\"->\"]?.maxRefAverage?.value;\n    const minDutStdDev1=msg.payload[\"->\"]?.flowTest[\"->\"]?.minDutStdDev?.value;\n    const maxDutStdDev1=msg.payload[\"->\"]?.flowTest[\"->\"]?.maxDutStdDev?.value;\n    const minRefStdDev1=msg.payload[\"->\"]?.flowTest[\"->\"]?.minRefStdDev?.value;\n    const maxRefStdDev1=msg.payload[\"->\"]?.flowTest[\"->\"]?.maxRefStdDev?.value;\n    const minDutTemperature1=msg.payload[\"->\"]?.flowTest[\"->\"]?.minDutTemperature?.value;\n    const maxDutTemperature1=msg.payload[\"->\"]?.flowTest[\"->\"]?.maxDutTemperature?.value;\n    const sizeFactor1=msg.payload[\"->\"]?.flowTest[\"->\"]?.sizeFactor?.value;\n    const returnCodeFT1=msg.payload[\"->\"]?.flowTest[\"->\"]?.returnCode?.value;\n    const testResultFT1=msg.payload[\"->\"]?.flowTest[\"->\"]?.testResult?.value;\n    const standardDeviationDut1=msg.payload[\"->\"]?.flowTest[\"->\"]?.standardDeviationDut?.value;\n    const standardDeviationRef1=msg.payload[\"->\"]?.flowTest[\"->\"]?.standardDeviationRef?.value;\n    const temperatureDut1=msg.payload[\"->\"]?.flowTest[\"->\"]?.temperatureDut?.value;\n    const temperatureRef1=msg.payload[\"->\"]?.flowTest[\"->\"]?.temperatureRef?.value;\n    const runningAverageDut1=msg.payload[\"->\"]?.flowTest[\"->\"]?.runningAverageDut?.value;\n    const runningAverageRef1=msg.payload[\"->\"]?.flowTest[\"->\"]?.runningAverageRef?.value;\n    const flowSource1=msg.payload[\"->\"]?.flowTest[\"->\"]?.flowSource?.value;\n    const samplesTaken1=msg.payload[\"->\"]?.flowTest[\"->\"]?.samplesTaken?.value;\n    const sampleTime1=msg.payload[\"->\"]?.flowTest[\"->\"]?.sampleTime?.value;\n\n    const prevSerialNumber=msg.payload[\"->\"]?.prevSerialNumber?.value;\n    const dataTimeStamp=msg.payload[\"->\"]?.dataTimeStamp?.value || '';\n\n    const productC=msg.payload[\"/\"]?.coil[\"->\"]?.product?.value;\n    const articleCodeC=msg.payload[\"/\"]?.coil[\"->\"]?.articleCode?.value;\n    const revisionC=msg.payload[\"/\"]?.coil[\"->\"]?.revision?.value;\n    const statusC=msg.payload[\"/\"]?.coil[\"->\"]?.status?.value;\n    const readCodeC=msg.payload[\"/\"]?.coil[\"->\"]?.readCode?.value;\n    const measuredTrayPosition=msg.payload[\"/\"]?.coil[\"->\"]?.measuredTrayPosition?.value;\n    //const measuredTrayPosition1=measuredTrayPosition?.substr(1,9)\n    //const measuredTrayPosition2=measuredTrayPosition?.substr(11,8)\n    const x=msg.payload[\"/\"]?.coil[\"->\"]?.measuredTrayPosition[\"->\"]?.x?.value;\n    const y=msg.payload[\"/\"]?.coil[\"->\"]?.measuredTrayPosition[\"->\"]?.y?.value;\n    const z=msg.payload[\"/\"]?.coil[\"->\"]?.measuredTrayPosition[\"->\"]?.z?.value;\n    const trayDeltaC=msg.payload[\"/\"]?.coil[\"->\"]?.trayDelta?.value;\n    const x1=msg.payload[\"/\"]?.coil[\"->\"]?.trayDelta[\"->\"]?.x?.value;\n    const y1=msg.payload[\"/\"]?.coil[\"->\"]?.trayDelta[\"->\"]?.y?.value;\n    const z1=msg.payload[\"/\"]?.coil[\"->\"]?.trayDelta[\"->\"]?.rz?.value;\n    const productUB=msg.payload[\"/\"]?.coil[\"/\"]?.uBracket[\"->\"]?.product?.value;\n    const statusUB=msg.payload[\"/\"]?.coil[\"/\"]?.uBracket[\"->\"]?.status?.value;\n\n    const productEC=msg.payload[\"/\"]?.eCable[\"->\"]?.product?.value;\n    const statusEC=msg.payload[\"/\"]?.eCable[\"->\"]?.status?.value;\n    const numberEC=msg.payload[\"/\"]?.eCable[\"->\"]?.number?.value;\n\n    const articleCodeT=msg.payload[\"/\"]?.tube[\"->\"]?.articleCode?.value;\n    const statusT=msg.payload[\"/\"]?.tube[\"->\"]?.status?.value;\n    const productT=msg.payload[\"/\"]?.tube[\"->\"]?.product?.value;\n    const trayCodeT=msg.payload[\"/\"]?.tube[\"->\"]?.trayCode?.value;\n    const cmdDispense1=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense1?.value;\n    const dispenseTime1=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense1[\"->\"]?.dispenseTime?.value;\n    const pressureSetPoint1=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense1[\"->\"]?.pressureSetPoint?.value;\n    const cmdDispense2=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense2?.value;\n    const dispenseTime2=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense2[\"->\"]?.dispenseTime?.value;\n    const pressureSetPoint2=msg.payload[\"/\"]?.tube[\"->\"]?.cmdDispense2[\"->\"]?.pressureSetPoint?.value;\n\n    const productG=msg.payload[\"/\"]?.tube[\"/\"]?.Grease[\"->\"]?.product?.value;\n    const statusG=msg.payload[\"/\"]?.tube[\"/\"]?.Grease[\"->\"]?.status?.value;\n\n    const productCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.product?.value;\n    const articleCodeCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.articleCode?.value;\n    const revisionCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.revision?.value;\n    const statusCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.status?.value;\n    const airPressureCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.airPressure?.value;\n    const airBackPressureCup=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"->\"]?.airBackPressure?.value;\n\n    const productOR=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.product?.value;\n    const articleCodeOR=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.articleCode?.value;\n    const revisionOR=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.revision?.value;\n    const statusOR=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.status?.value;\n    const minGreyValue=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.minGreyValue?.value;\n    const maxGreyValue=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.maxGreyValue?.value;\n    const avgGreyScale=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.avgGreyScale?.value;\n    const lowestGreyScale=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.lowestGreyScale?.value;\n    const highestGreyScale=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.highestGreyScale?.value;\n    const stdDevGreyScale=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.stdDevGreyScale?.value;\n    const batchCode=msg.payload[\"/\"]?.tube[\"/\"]?.cup[\"/\"]?.oRing[\"->\"]?.batchCode?.value;\n\nvar msg1={};\nvar stringy=\"INSERT INTO [DSDB].[dbo].[ATMEndProduct] ([product] ,[articleCode] ,[revision] ,[recipe] ,[recipeRevision] ,[serialNumber] ,[orderNumber] ,[labelCode] ,[readCode] ,[status] ,[solderingStation] ,[solderTime] ,[cooldownTime] ,[power] ,[weld] ,[rotatorStartPosition] ,[rotatorDegToWeld] ,[rotatorWeldSpeed] ,[laserPowerWatt] ,[laserStartDelay] ,[clampPressure] ,[testResult] ,[pressurize] ,[setPressure] ,[allowedError] ,[stabilisationTime] ,[isolationTime] ,[flowTime] ,[maxTime] ,[returnCodeP] ,[isolatedPressure] ,[pressureDropTest] ,[testTime] ,[allowedStartError] ,[setTestPressure] ,[allowedDrop] ,[returnCodePD] ,[startPressure] ,[endPressure] ,[testResultPD] ,[impedanceTest] ,[frequency1] ,[frequency2] ,[voltageLevel] ,[minImpedance1] ,[minImpedance2] ,[maxImpedance1] ,[maxImpedance2] ,[minPhaseAngle1] ,[minPhaseAngle2] ,[maxPhaseAngle1] ,[maxPhaseAngle2] ,[requiredSerialNumber] ,[returnCodeIT] ,[testResultIT] ,[leftImpedance1] ,[leftImpedance2] ,[leftPhaseAngle1] ,[leftPhaseAngle2] ,[rightImpedance1] ,[rightImpedance2] ,[rightPhaseAngle1] ,[rightPhaseAngle2] ,[actualSerialNumber] ,[zeroFlowTest] ,[numberOfSamples] ,[ticksPerSample] ,[dutSerialNumber] ,[minDutStdDev] ,[maxDutStdDev] ,[minDutAverage] ,[maxDutAverage] ,[returnCodeZFT] ,[testResultZFT] ,[dutSensorStatus] ,[standardDeviationDut] ,[runningAverageDut] ,[flowSource] ,[samplesTaken] ,[sampleTime] ,[refSensorStatus] ,[setFlow] ,[stabilitsationTimeSF] ,[flowTest] ,[numberOfSamples1] ,[ticksPerSample1] ,[dutSerialNumber1] ,[refSerialNumber1] ,[minDutAverageDeviation1] ,[maxDutAverageDeviation1] ,[minRefAverage1] ,[maxRefAverage] ,[minDutStdDev1] ,[maxDutStdDev1] ,[minRefStdDev1] ,[maxRefStdDev1] ,[maxDutTemperature1] ,[minDutTemperature1] ,[sizeFactor1] ,[returnCodeFT1] ,[testResultFT1] ,[standardDeviationDut1] ,[standardDeviationRef1] ,[temperatureDut1] ,[temperatureRef1] ,[runningAverageDut1] ,[runningAverageRef1] ,[sampleTime1] ,[flowSource1] ,[samplesTaken1] ,[prevSerialNumber] ,[dataTimeStamp] ,[productC] ,[articleCodeC] ,[revisionC] ,[statusC] ,[readCodeC] ,[measuredTrayPosition1] ,[x] ,[y] ,[z] ,[trayDeltaC] ,[x1] ,[y1] ,[z1] ,[productUB] ,[statusUB] ,[productEC] ,[statusEC] ,[numberEC] ,[articleCodeT] ,[statusT] ,[productT] ,[trayCodeT] ,[cmdDispense1] ,[dispenseTime1] ,[pressureSetPoint1] ,[cmdDispense2] ,[dispenseTime2] ,[pressureSetPoint2] ,[productG] ,[statusG] ,[productCup] ,[articleCodeCup] ,[revisionCup] ,[statusCup] ,[airPressureCup] ,[airBackPressureCup] ,[productOR] ,[articleCodeOR] ,[revisionOR] ,[statusOR] ,[minGreyValue] ,[maxGreyValue] ,[avgGreyScale] ,[lowestGreyScale] ,[highestGreyScale] ,[stdDevGreyScale] ,[batchCode]) VALUES ('\"+product+\"','\"+articleCode+\"','\"+revision+\"','\"+recipe+\"','\"+recipeRevision+\"','\"+serialNumber+\"','\"+orderNumber+\"','\"+labelCode+\"','\"+readCode+\"','\"+status+\"','\"+solderStation+\"','\"+solderTime+\"','\"+cooldownTime+\"','\"+power+\"','\"+weld+\"','\"+rotatorStartPosition+\"','\"+rotatorDegToWeld+\"','\"+rotatorWeldSpeed+\"','\"+laserPowerWatt+\"','\"+laserStartDelay+\"','\"+clampPressure+\"','\"+testResult+\"','\"+pressurize+\"','\"+setPressure+\"','\"+allowedError+\"','\"+stabilisationTime+\"','\"+isolationTime+\"','\"+flowTime+\"','\"+maxTime+\"','\"+returnCode+\"','\"+isolatedPressure+\"','\"+pressureDropTest+\"','\"+testTime+\"','\"+allowedStartError+\"','\"+setTestPressure+\"','\"+allowedDrop+\"','\"+returnCodePD+\"','\"+startPressure+\"','\"+endPressure+\"','\"+testResultPD+\"','\"+impedanceTest+\"','\"+frequency1+\"','\"+frequency2+\"','\"+voltageLevel+\"','\"+minImpedance1+\"','\"+minImpedance2+\"','\"+maxImpedance1+\"','\"+maxImpedance2+\"','\"+minPhaseAngle1+\"','\"+minPhaseAngle2+\"','\"+maxPhaseAngle1+\"','\"+maxPhaseAngle2+\"','\"+requiredSerialNumber+\"','\"+returnCodeIT+\"','\"+testResultIT+\"','\"+leftImpedance1+\"','\"+leftImpedance2+\"','\"+leftPhaseAngle1+\"','\"+leftPhaseAngle2+\"','\"+rightImpedance1+\"','\"+rightImpedance2+\"','\"+rightPhaseAngle1+\"','\"+rightPhaseAngle2+\"','\"+actualSerialNumber+\"','\"+zeroFlowTest+\"','\"+numberOfSamples+\"','\"+ticksPerSample+\"','\"+dutSerialNumber+\"','\"+minDutStdDev+\"','\"+maxDutStdDev+\"','\"+minDutAverage+\"','\"+maxDutAverage+\"','\"+returnCodeZFT+\"','\"+testResultZFT+\"','\"+dutSensorStatus+\"','\"+standardDeviationDut+\"','\"+runningAverageDut+\"','\"+flowSource+\"','\"+samplesTaken+\"','\"+sampleTime+\"','\"+refSensorStatus+\"','\"+setFlow+\"','\"+stabilisationTimeSF+\"','\"+flowTest+\"','\"+numberOfSamples1+\"','\"+ticksPerSample1+\"','\"+dutSerialNumber1+\"','\"+refSerialNumber1+\"','\"+minDutAverageDeviation1+\"','\"+maxDutAverageDeviation1+\"','\"+minRefAverage1+\"','\"+maxRefAverage1+\"','\"+minDutStdDev1+\"','\"+maxDutStdDev1+\"','\"+minRefStdDev1+\"','\"+maxRefStdDev1+\"','\"+minDutTemperature1+\"','\"+maxDutTemperature1+\"','\"+sizeFactor1+\"','\"+returnCodeFT1+\"','\"+testResultFT1+\"','\"+standardDeviationDut1+\"','\"+standardDeviationRef1+\"','\"+temperatureDut1+\"','\"+temperatureRef1+\"','\"+runningAverageDut1+\"','\"+runningAverageRef1+\"','\"+flowSource1+\"','\"+samplesTaken1+\"','\"+sampleTime1+\"','\"+prevSerialNumber+\"','\"+dataTimeStamp+\"','\"+productC+\"','\"+articleCodeC+\"','\"+revisionC+\"','\"+statusC+\"','\"+readCodeC+\"','\"+measuredTrayPosition+\"','\"+x+\"','\"+y+\"','\"+z+\"','\"+trayDeltaC+\"','\"+x1+\"','\"+y1+\"','\"+z1+\"','\"+productUB+\"','\"+statusUB+\"','\"+productEC+\"','\"+statusEC+\"','\"+numberEC+\"','\"+articleCodeT+\"','\"+statusT+\"','\"+productT+\"','\"+trayCodeT+\"','\"+cmdDispense1+\"','\"+dispenseTime1+\"','\"+pressureSetPoint1+\"','\"+cmdDispense2+\"','\"+dispenseTime2+\"','\"+pressureSetPoint2+\"','\"+productG+\"','\"+statusG+\"','\"+productCup+\"','\"+articleCodeCup+\"','\"+revisionCup+\"','\"+statusCup+\"','\"+airPressureCup+\"','\"+airBackPressureCup+\"','\"+productOR+\"','\"+articleCodeOR+\"','\"+revisionOR+\"','\"+statusOR+\"','\"+minGreyValue+\"','\"+maxGreyValue+\"','\"+avgGreyScale+\"','\"+lowestGreyScale+\"','\"+highestGreyScale+\"','\"+stdDevGreyScale+\"','\"+batchCode+\"')\";\nmsg1.payload=stringy;\nreturn msg1;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 560,
        "y": 300,
        "wires": [
            [
                "b0b216acd5256121",
                "6529a2c4fbd46724"
            ]
        ]
    },
    {
        "id": "b0b216acd5256121",
        "type": "debug",
        "z": "9eb09f2c99f04cac",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 730,
        "y": 300,
        "wires": []
    },
    {
        "id": "14ebf81614eaa7be",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "GauravTest",
        "server": "10.51.1.16",
        "port": "1433",
        "encyption": false,
        "trustServerCertificate": true,
        "database": "DSDB",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "100",
        "parseJSON": true,
        "enableArithAbort": true
    },
    {
        "id": "7f92728b10ba1af4",
        "type": "mqtt-broker",
        "name": "ATM Machine",
        "broker": "10.51.171.10",
        "port": "1883",
        "clientid": "node-red",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "sessionExpiry": ""
    }
]

This is now my flow. Function node has been updated.

{"->":{"product":{"value":"ATM"},"articleCode":{"value":"4006110801"},"revision":{"value":"003"},"recipe":{"value":"4006110801 ATM DN25 FKM"},"recipeRevision":{"value":"001"},"serialNumber":{"value":"22815021"},"orderNumber":{"value":"dn25fkm 6-10-2022"},"labelCode":{"value":"4006110801_003_22815021_X"},"readCode":{"value":"QG92"},"status":{"value":-4},"dataTimeStamp":{"value":"2022-10-06T06:35:16.676Z"}},"/":{"coil":{"->":{"product":{"value":"Coil"},"articleCode":{"value":"4008043701"},"revision":{"value":"000"},"status":{"value":4},"readCode":{"value":"4008043701 000 2209 38975 60@ 1600"},"measuredTrayPosition":{"value":1612,"->":{"x":{"value":63},"y":{"value":0},"z":{"value":0}}},"trayDelta":{"value":225,"->":{"x":{"value":2.21402},"y":{"value":-0.914638},"rz":{"value":0.491452}}}}}}}

For above JSON i get an error. Cannot read properties of undefined reading ('->').

{"->":{"product":{"value":"ATM"},"articleCode":{"value":"4006110801"},"revision":{"value":"003"},"recipe":{"value":"4006110801 ATM DN25 FKM"},"recipeRevision":{"value":"001"},"serialNumber":{"value":"22815022"},"orderNumber":{"value":"dn25fkm 6-10-2022"},"labelCode":{"value":"4006110801_003_22815022_X"},"readCode":{"value":"4006110801_003_22815022_X"},"status":{"value":-17},"solderingStation":{"value":"4006110801_003_22815021_X","->":{"solderTime":{"value":4000},"cooldownTime":{"value":2000},"power":{"value":99}}},"weld":{"value":"003","->":{"rotatorStartPosition":{"value":4},"rotatorDegToWeld":{"value":-7},"rotatorWeldSpeed":{"value":31.8},"laserPowerWatt":{"value":600},"laserStartDelay":{"value":10},"clampPressure":{"value":2.5}}},"testResult":{"value":-1017},"pressurize":{"->":{"setPressure":{"value":24},"allowedError":{"value":5},"stabilisationTime":{"value":1000},"isolationTime":{"value":15000},"flowTime":{"value":10000},"maxTime":{"value":5000},"returnCode":{"value":2},"isolatedPressure":{"value":24.216668}}},"pressureDropTest":{"->":{"testTime":{"value":30000},"allowedStartError":{"value":5},"setTestPressure":{"value":24},"allowedDrop":{"value":2.5},"returnCode":{"value":2},"startPressure":{"value":24.216101},"endPressure":{"value":23.8054},"testResult":{"value":2}}},"impedanceTest":{"->":{"frequencies":{"value":"[20.000,2000.000]"},"voltageLevel":{"value":0.1},"minImpedances":{"value":"[3000.000,0.000]"},"maxImpedances":{"value":"[25000.000,100000.000]"},"minPhaseAngle":{"value":"[-90.000,-15.000]"},"maxPhaseAngle":{"value":"[0.000,0.000]"},"requiredSerialNumber":{"value":""},"returnCode":{"value":2},"testResult":{"value":-1000},"leftImpedances":{"value":"[334099.000,9472.840]"},"leftPhaseAngle":{"value":"[-66.765,-40.662]"},"rightImpedances":{"value":"[35123.099,6916.809]"},"rightPhaseAngle":{"value":"[-47.940,-9.921]"},"actualSerialNumber":{"value":"GER880566"}}},"zeroFlowTest":{"->":{"numberOfSamples":{"value":50},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"minDutStdDev":{"value":0},"maxDutStdDev":{"value":0.008},"minDutAverage":{"value":-0.002},"maxDutAverage":{"value":0.002},"returnCode":{"value":2},"testResult":{"value":-1003},"dutSensorStatus":{"value":100512},"standardDeviationDut":{"value":0.002297},"runningAverageDut":{"value":0.002623},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50},"sampleTime":{"value":12860},"refSensorStatus":{"value":0}}},"setFlow":{"->":{"stabilisationTime":{"value":8000}}},"flowTest":{"->":{"numberOfSamples":{"value":50},"ticksPerSample":{"value":25},"dutSerialNumber":{"value":"TESTATM01"},"refSerialNumber":{"value":"20180612_30"},"minDutAverageDeviation":{"value":-15},"maxDutAverageDeviation":{"value":10},"minRefAverage":{"value":0.35},"maxRefAverage":{"value":0.45},"minDutStdDev":{"value":0.0001},"maxDutStdDev":{"value":0.02},"minRefStdDev":{"value":0.0001},"maxRefStdDev":{"value":0.01},"maxDutTemperature":{"value":999999999999.9},"minDutTemperature":{"value":-999999999999.9},"sizeFactor":{"value":4.4685},"returnCode":{"value":2},"testResult":{"value":2},"standardDeviationDut":{"value":0.008876},"standardDeviationRef":{"value":0.003016},"temperatureDut":{"value":-55},"temperatureRef":{"value":25.457718},"runningAverageDut":{"value":0.384005},"runningAverageRef":{"value":0.415854},"sampleTime":{"value":16280},"flowSource":{"value":"ATMObj1200"},"samplesTaken":{"value":50}}},"dataTimeStamp":{"value":"2022-10-06T06:43:20.916Z"}},"/":{"coil":{"->":{"product":{"value":"Coil"},"articleCode":{"value":"4008043701"},"revision":{"value":"000"},"status":{"value":4},"readCode":{"value":"4008043701 000 2209 38973 60@ 1600"},"measuredTrayPosition":{"value":1592,"->":{"x":{"value":126},"y":{"value":0},"z":{"value":0}}},"trayDelta":{"value":225,"->":{"x":{"value":2.377136},"y":{"value":-1.259794},"rz":{"value":0.434165}}}},"/":{"uBracket":{"->":{"product":{"value":"Ubracket"},"status":{"value":1}}}}},"eCable":{"->":{"product":{"value":"Ecable"},"status":{"value":2},"number":{"value":18}}},"tube":{"->":{"articleCode":{"value":"4005945302"},"status":{"value":6},"product":{"value":"Tube"},"trayCode":{"value":"00434 4005945202 003"},"rawAlignPosition":{"value":930},"maxAlignPosition":{"value":950},"minAlignPosition":{"value":925},"rawPressPosition":{"value":967},"maxPressPosition":{"value":970},"minPressPosition":{"value":960},"cmdDispense1":{"value":970,"->":{"dispenseTime":{"value":40},"pressureSetPoint":{"value":4.1}}},"cmdDispense2":{"value":"2022-10-06T06:35:16.676Z","->":{"dispenseTime":{"value":45},"pressureSetPoint":{"value":4.1}}}},"/":{"Grease":{"->":{"product":{"value":"Grease"},"status":{"value":1}}},"cup":{"->":{"product":{"value":"Temperature insert"},"articleCode":{"value":""},"revision":{"value":""},"status":{"value":2},"airPressure":{"value":5},"airBackPressure":{"value":2.5}},"/":{"oRing":{"->":{"product":{"value":"oRing"},"articleCode":{"value":"060018V8836S190"},"revision":{"value":"000"},"status":{"value":2},"minGreyValue":{"value":90},"maxGreyValue":{"value":225},"avgGreyScale":{"value":128},"lowestGreyScale":{"value":79},"highestGreyScale":{"value":220},"stdDevGreyScale":{"value":1581},"batchCode":{"value":"0093561"}}}}}}}}}

For this JSON I do not get an error despite having missing fields.

Suggestions ?

Yes, you need to check properties exist BEFORE square bracket notation as well....

const rotatorStartPosition=msg.payload["->"]?.weld["->"]?.rotatorStartPosition?.value;

... will fail if weld is undefined. So add optional chaining so that weld is checked before trying to access ["->"]...

const rotatorStartPosition=msg.payload?.["->"]?.weld?.["->"]?.rotatorStartPosition?.value;



Demo...

The demo flow...

[{"id":"9a6094d969c93867","type":"function","z":"c876e57260edd2ab","name":"","func":"    const product=msg.payload?.[\"->\"]?.product?.value;\n    const articleCode=msg.payload?.[\"->\"]?.articleCode?.value;\n    const revision=msg.payload?.[\"->\"]?.revision?.value;\n    const recipe=msg.payload?.[\"->\"]?.recipe?.value;\n    const recipeRevision=msg.payload?.[\"->\"]?.recipeRevision?.value;\n    const serialNumber=msg.payload?.[\"->\"]?.serialNumber?.value;\n    const orderNumber=msg.payload?.[\"->\"]?.orderNumber?.value;\n    const labelCode=msg.payload?.[\"->\"]?.labelCode?.value;\n    const readCode=msg.payload?.[\"->\"]?.readCode?.value;\n    const status=msg.payload?.[\"->\"]?.status?.value;\n    const solderStation=msg.payload?.[\"->\"]?.solderingStation?.value;\n    const solderTime=msg.payload?.[\"->\"]?.solderingStation?.[\"->\"]?.solderTime?.value;\n    const cooldownTime=msg.payload?.[\"->\"]?.solderingStation?.[\"->\"]?.cooldownTime?.value;\n    const power=msg.payload?.[\"->\"]?.solderingStation?.[\"->\"]?.power?.value;\n\n    const weld=msg.payload?.[\"->\"]?.weld?.value;\n    const rotatorStartPosition=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.rotatorStartPosition?.value;\n    const rotatorDegToWeld=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.rotatorDegToWeld?.value;\n    const rotatorWeldSpeed=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.rotatorWeldSpeed?.value;\n    const laserPowerWatt=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.laserPowerWatt?.value;\n    const laserStartDelay=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.laserStartDelay?.value;\n    const clampPressure=msg.payload?.[\"->\"]?.weld?.[\"->\"]?.clampPressure?.value;\n\n    const testResult=msg.payload?.[\"->\"]?.testResult?.value;\n    const pressurize=msg.payload?.[\"->\"]?.pressurize?.value;\n    const setPressure=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.setPressure?.value;\n    const allowedError=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.allowedError?.value;\n    const stabilisationTime=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.stabilisationTime?.value;\n    const isolationTime=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.isolationTime?.value;\n    const flowTime=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.flowTime?.value;\n    const maxTime=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.maxTime?.value;\n    const returnCode=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.returnCode?.value;\n    const isolatedPressure=msg.payload?.[\"->\"]?.pressurize?.[\"->\"]?.isolatedPressure?.value;\n\n    const pressureDropTest=msg.payload?.[\"->\"]?.pressureDropTest?.value;\n    const testTime=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.testTime?.value;\n    const allowedStartError=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.allowedStartError?.value;\n    const setTestPressure=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.setTestPressure?.value;\n    const allowedDrop=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.allowedDrop?.value;\n    const returnCodePD=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.returnCode?.value;\n    const startPressure=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.startPressure?.value;\n    const endPressure=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.endPressure?.value;\n    const testResultPD=msg.payload?.[\"->\"]?.pressureDropTest?.[\"->\"]?.testResult?.value;\n\n    const impedanceTest=msg.payload?.[\"->\"]?.impedanceTest?.value;\n    const frequency=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.frequencies?.value;\n    const frequency1=frequency?.substr(1,6)\n    const frequency2=frequency?.substr(8,8)\n    const voltageLevel=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.voltageLevel?.value;\n    const minImpedances=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.minImpedances?.value;\n    const minImpedance1=minImpedances?.substr(1,8)\n    const minImpedance2=minImpedances?.substr(10,5)\n    const maxImpedances=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.maxImpedances?.value;\n    const maxImpedance1=maxImpedances?.substr(1,9)\n    const maxImpedance2=maxImpedances?.substr(11,10)\n    const minPhaseAngle=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.minPhaseAngle?.value;\n    const minPhaseAngle1=minPhaseAngle?.substr(1,7)\n    const minPhaseAngle2=minPhaseAngle?.substr(9,7)\n    const maxPhaseAngle=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.maxPhaseAngle?.value;\n    const maxPhaseAngle1=maxPhaseAngle?.substr(1,5)\n    const maxPhaseAngle2=maxPhaseAngle?.substr(7,5)\n    const requiredSerialNumber=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.requiredSerialNumber?.value;\n    const returnCodeIT=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.returnCode?.value;\n    const testResultIT=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.testResult?.value;\n    const leftImpedances=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.leftImpedances?.value;\n    const leftImpedance1=leftImpedances?.substr(1,9)\n    const leftImpedance2=leftImpedances?.substr(11,8)\n    const leftPhaseAngle=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.leftPhaseAngle?.value;\n    const leftPhaseAngle1=leftPhaseAngle?.substr(1,7)\n    const leftPhaseAngle2=leftPhaseAngle?.substr(9,6)\n    const rightImpedances=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.rightImpedances?.value;\n    const rightImpedance1=rightImpedances?.substr(1,9)\n    const rightImpedance2=rightImpedances?.substr(11,8)\n    const rightPhaseAngle=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.rightPhaseAngle?.value;\n    const rightPhaseAngle1=rightPhaseAngle?.substr(1,7)\n    const rightPhaseAngle2=rightPhaseAngle?.substr(9,6)\n    const actualSerialNumber=msg.payload?.[\"->\"]?.impedanceTest?.[\"->\"]?.actualSerialNumber?.value;\n\n    const zeroFlowTest=msg.payload?.[\"->\"]?.zeroFlowTest?.value;\n    const numberOfSamples=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.numberOfSamples?.value;\n    const ticksPerSample=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.ticksPerSample?.value;\n    const dutSerialNumber=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.dutSerialNumber?.value;\n    const minDutStdDev=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.minDutStdDev?.value;\n    const maxDutStdDev=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.maxDutStdDev?.value;\n    const minDutAverage=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.minDutAverage?.value;\n    const maxDutAverage=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.maxDutAverage?.value;\n    const returnCodeZFT=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.returnCode?.value;\n    const testResultZFT=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.testResult?.value;\n    const dutSensorStatus=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.dutSensorStatus?.value;\n    const standardDeviationDut=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.standardDeviationDut?.value;\n    const runningAverageDut=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.runningAverageDut?.value;\n    const flowSource=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.flowSource?.value;\n    const samplesTaken=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.samplesTaken?.value;\n    const sampleTime=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.sampleTime?.value;\n    const refSensorStatus=msg.payload?.[\"->\"]?.zeroFlowTest?.[\"->\"]?.refSensorStatus?.value;\n\n    const setFlow=msg.payload?.[\"->\"]?.setFlow?.value;\n    const stabilisationTimeSF=msg.payload?.[\"->\"]?.setFlow?.[\"->\"]?.stabilisationTime?.value;\n\n    const flowTest=msg.payload?.[\"->\"]?.flowTest?.value;\n    const numberOfSamples1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.numberOfSamples?.value;\n    const ticksPerSample1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.ticksPerSample?.value;\n    const dutSerialNumber1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.dutSerialNumber?.value;\n    const refSerialNumber1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.refSerialNumber?.value;\n    const minDutAverageDeviation1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.minDutAverageDeviation?.value;\n    const maxDutAverageDeviation1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.maxDutAverageDeviation?.value;\n    const minRefAverage1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.minRefAverage?.value;\n    const maxRefAverage1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.maxRefAverage?.value;\n    const minDutStdDev1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.minDutStdDev?.value;\n    const maxDutStdDev1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.maxDutStdDev?.value;\n    const minRefStdDev1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.minRefStdDev?.value;\n    const maxRefStdDev1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.maxRefStdDev?.value;\n    const minDutTemperature1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.minDutTemperature?.value;\n    const maxDutTemperature1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.maxDutTemperature?.value;\n    const sizeFactor1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.sizeFactor?.value;\n    const returnCodeFT1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.returnCode?.value;\n    const testResultFT1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.testResult?.value;\n    const standardDeviationDut1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.standardDeviationDut?.value;\n    const standardDeviationRef1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.standardDeviationRef?.value;\n    const temperatureDut1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.temperatureDut?.value;\n    const temperatureRef1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.temperatureRef?.value;\n    const runningAverageDut1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.runningAverageDut?.value;\n    const runningAverageRef1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.runningAverageRef?.value;\n    const flowSource1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.flowSource?.value;\n    const samplesTaken1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.samplesTaken?.value;\n    const sampleTime1=msg.payload?.[\"->\"]?.flowTest?.[\"->\"]?.sampleTime?.value;\n\n    const prevSerialNumber=msg.payload?.[\"->\"]?.prevSerialNumber?.value;\n    const dataTimeStamp=msg.payload?.[\"->\"]?.dataTimeStamp?.value || '';\n\n    const productC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.product?.value;\n    const articleCodeC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.articleCode?.value;\n    const revisionC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.revision?.value;\n    const statusC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.status?.value;\n    const readCodeC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.readCode?.value;\n    const measuredTrayPosition=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.measuredTrayPosition?.value;\n    //const measuredTrayPosition1=measuredTrayPosition??.substr(1,9)\n    //const measuredTrayPosition2=measuredTrayPosition??.substr(11,8)\n    const x=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.measuredTrayPosition?.[\"->\"]?.x?.value;\n    const y=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.measuredTrayPosition?.[\"->\"]?.y?.value;\n    const z=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.measuredTrayPosition?.[\"->\"]?.z?.value;\n    const trayDeltaC=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.trayDelta?.value;\n    const x1=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.trayDelta?.[\"->\"]?.x?.value;\n    const y1=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.trayDelta?.[\"->\"]?.y?.value;\n    const z1=msg.payload?.[\"/\"]?.coil?.[\"->\"]?.trayDelta?.[\"->\"]?.rz?.value;\n    const productUB=msg.payload?.[\"/\"]?.coil?.[\"/\"]?.uBracket?.[\"->\"]?.product?.value;\n    const statusUB=msg.payload?.[\"/\"]?.coil?.[\"/\"]?.uBracket?.[\"->\"]?.status?.value;\n\n    const productEC=msg.payload?.[\"/\"]?.eCable?.[\"->\"]?.product?.value;\n    const statusEC=msg.payload?.[\"/\"]?.eCable?.[\"->\"]?.status?.value;\n    const numberEC=msg.payload?.[\"/\"]?.eCable?.[\"->\"]?.number?.value;\n\n    const articleCodeT=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.articleCode?.value;\n    const statusT=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.status?.value;\n    const productT=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.product?.value;\n    const trayCodeT=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.trayCode?.value;\n    const cmdDispense1=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense1?.value;\n    const dispenseTime1=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense1?.[\"->\"]?.dispenseTime?.value;\n    const pressureSetPoint1=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense1?.[\"->\"]?.pressureSetPoint?.value;\n    const cmdDispense2=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense2?.value;\n    const dispenseTime2=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense2?.[\"->\"]?.dispenseTime?.value;\n    const pressureSetPoint2=msg.payload?.[\"/\"]?.tube?.[\"->\"]?.cmdDispense2?.[\"->\"]?.pressureSetPoint?.value;\n\n    const productG=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.Grease?.[\"->\"]?.product?.value;\n    const statusG=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.Grease?.[\"->\"]?.status?.value;\n\n    const productCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.product?.value;\n    const articleCodeCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.articleCode?.value;\n    const revisionCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.revision?.value;\n    const statusCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.status?.value;\n    const airPressureCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.airPressure?.value;\n    const airBackPressureCup=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"->\"]?.airBackPressure?.value;\n\n    const productOR=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.product?.value;\n    const articleCodeOR=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.articleCode?.value;\n    const revisionOR=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.revision?.value;\n    const statusOR=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.status?.value;\n    const minGreyValue=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.minGreyValue?.value;\n    const maxGreyValue=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.maxGreyValue?.value;\n    const avgGreyScale=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.avgGreyScale?.value;\n    const lowestGreyScale=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.lowestGreyScale?.value;\n    const highestGreyScale=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.highestGreyScale?.value;\n    const stdDevGreyScale=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.stdDevGreyScale?.value;\n    const batchCode=msg.payload?.[\"/\"]?.tube?.[\"/\"]?.cup?.[\"/\"]?.oRing?.[\"->\"]?.batchCode?.value;\n\nvar msg1={};\nvar stringy=\"INSERT INTO [DSDB].[dbo].[ATMEndProduct] ([product] ,[articleCode] ,[revision] ,[recipe] ,[recipeRevision] ,[serialNumber] ,[orderNumber] ,[labelCode] ,[readCode] ,[status] ,[solderingStation] ,[solderTime] ,[cooldownTime] ,[power] ,[weld] ,[rotatorStartPosition] ,[rotatorDegToWeld] ,[rotatorWeldSpeed] ,[laserPowerWatt] ,[laserStartDelay] ,[clampPressure] ,[testResult] ,[pressurize] ,[setPressure] ,[allowedError] ,[stabilisationTime] ,[isolationTime] ,[flowTime] ,[maxTime] ,[returnCodeP] ,[isolatedPressure] ,[pressureDropTest] ,[testTime] ,[allowedStartError] ,[setTestPressure] ,[allowedDrop] ,[returnCodePD] ,[startPressure] ,[endPressure] ,[testResultPD] ,[impedanceTest] ,[frequency1] ,[frequency2] ,[voltageLevel] ,[minImpedance1] ,[minImpedance2] ,[maxImpedance1] ,[maxImpedance2] ,[minPhaseAngle1] ,[minPhaseAngle2] ,[maxPhaseAngle1] ,[maxPhaseAngle2] ,[requiredSerialNumber] ,[returnCodeIT] ,[testResultIT] ,[leftImpedance1] ,[leftImpedance2] ,[leftPhaseAngle1] ,[leftPhaseAngle2] ,[rightImpedance1] ,[rightImpedance2] ,[rightPhaseAngle1] ,[rightPhaseAngle2] ,[actualSerialNumber] ,[zeroFlowTest] ,[numberOfSamples] ,[ticksPerSample] ,[dutSerialNumber] ,[minDutStdDev] ,[maxDutStdDev] ,[minDutAverage] ,[maxDutAverage] ,[returnCodeZFT] ,[testResultZFT] ,[dutSensorStatus] ,[standardDeviationDut] ,[runningAverageDut] ,[flowSource] ,[samplesTaken] ,[sampleTime] ,[refSensorStatus] ,[setFlow] ,[stabilitsationTimeSF] ,[flowTest] ,[numberOfSamples1] ,[ticksPerSample1] ,[dutSerialNumber1] ,[refSerialNumber1] ,[minDutAverageDeviation1] ,[maxDutAverageDeviation1] ,[minRefAverage1] ,[maxRefAverage] ,[minDutStdDev1] ,[maxDutStdDev1] ,[minRefStdDev1] ,[maxRefStdDev1] ,[maxDutTemperature1] ,[minDutTemperature1] ,[sizeFactor1] ,[returnCodeFT1] ,[testResultFT1] ,[standardDeviationDut1] ,[standardDeviationRef1] ,[temperatureDut1] ,[temperatureRef1] ,[runningAverageDut1] ,[runningAverageRef1] ,[sampleTime1] ,[flowSource1] ,[samplesTaken1] ,[prevSerialNumber] ,[dataTimeStamp] ,[productC] ,[articleCodeC] ,[revisionC] ,[statusC] ,[readCodeC] ,[measuredTrayPosition1] ,[x] ,[y] ,[z] ,[trayDeltaC] ,[x1] ,[y1] ,[z1] ,[productUB] ,[statusUB] ,[productEC] ,[statusEC] ,[numberEC] ,[articleCodeT] ,[statusT] ,[productT] ,[trayCodeT] ,[cmdDispense1] ,[dispenseTime1] ,[pressureSetPoint1] ,[cmdDispense2] ,[dispenseTime2] ,[pressureSetPoint2] ,[productG] ,[statusG] ,[productCup] ,[articleCodeCup] ,[revisionCup] ,[statusCup] ,[airPressureCup] ,[airBackPressureCup] ,[productOR] ,[articleCodeOR] ,[revisionOR] ,[statusOR] ,[minGreyValue] ,[maxGreyValue] ,[avgGreyScale] ,[lowestGreyScale] ,[highestGreyScale] ,[stdDevGreyScale] ,[batchCode]) VALUES ('\"+product+\"','\"+articleCode+\"','\"+revision+\"','\"+recipe+\"','\"+recipeRevision+\"','\"+serialNumber+\"','\"+orderNumber+\"','\"+labelCode+\"','\"+readCode+\"','\"+status+\"','\"+solderStation+\"','\"+solderTime+\"','\"+cooldownTime+\"','\"+power+\"','\"+weld+\"','\"+rotatorStartPosition+\"','\"+rotatorDegToWeld+\"','\"+rotatorWeldSpeed+\"','\"+laserPowerWatt+\"','\"+laserStartDelay+\"','\"+clampPressure+\"','\"+testResult+\"','\"+pressurize+\"','\"+setPressure+\"','\"+allowedError+\"','\"+stabilisationTime+\"','\"+isolationTime+\"','\"+flowTime+\"','\"+maxTime+\"','\"+returnCode+\"','\"+isolatedPressure+\"','\"+pressureDropTest+\"','\"+testTime+\"','\"+allowedStartError+\"','\"+setTestPressure+\"','\"+allowedDrop+\"','\"+returnCodePD+\"','\"+startPressure+\"','\"+endPressure+\"','\"+testResultPD+\"','\"+impedanceTest+\"','\"+frequency1+\"','\"+frequency2+\"','\"+voltageLevel+\"','\"+minImpedance1+\"','\"+minImpedance2+\"','\"+maxImpedance1+\"','\"+maxImpedance2+\"','\"+minPhaseAngle1+\"','\"+minPhaseAngle2+\"','\"+maxPhaseAngle1+\"','\"+maxPhaseAngle2+\"','\"+requiredSerialNumber+\"','\"+returnCodeIT+\"','\"+testResultIT+\"','\"+leftImpedance1+\"','\"+leftImpedance2+\"','\"+leftPhaseAngle1+\"','\"+leftPhaseAngle2+\"','\"+rightImpedance1+\"','\"+rightImpedance2+\"','\"+rightPhaseAngle1+\"','\"+rightPhaseAngle2+\"','\"+actualSerialNumber+\"','\"+zeroFlowTest+\"','\"+numberOfSamples+\"','\"+ticksPerSample+\"','\"+dutSerialNumber+\"','\"+minDutStdDev+\"','\"+maxDutStdDev+\"','\"+minDutAverage+\"','\"+maxDutAverage+\"','\"+returnCodeZFT+\"','\"+testResultZFT+\"','\"+dutSensorStatus+\"','\"+standardDeviationDut+\"','\"+runningAverageDut+\"','\"+flowSource+\"','\"+samplesTaken+\"','\"+sampleTime+\"','\"+refSensorStatus+\"','\"+setFlow+\"','\"+stabilisationTimeSF+\"','\"+flowTest+\"','\"+numberOfSamples1+\"','\"+ticksPerSample1+\"','\"+dutSerialNumber1+\"','\"+refSerialNumber1+\"','\"+minDutAverageDeviation1+\"','\"+maxDutAverageDeviation1+\"','\"+minRefAverage1+\"','\"+maxRefAverage1+\"','\"+minDutStdDev1+\"','\"+maxDutStdDev1+\"','\"+minRefStdDev1+\"','\"+maxRefStdDev1+\"','\"+minDutTemperature1+\"','\"+maxDutTemperature1+\"','\"+sizeFactor1+\"','\"+returnCodeFT1+\"','\"+testResultFT1+\"','\"+standardDeviationDut1+\"','\"+standardDeviationRef1+\"','\"+temperatureDut1+\"','\"+temperatureRef1+\"','\"+runningAverageDut1+\"','\"+runningAverageRef1+\"','\"+flowSource1+\"','\"+samplesTaken1+\"','\"+sampleTime1+\"','\"+prevSerialNumber+\"','\"+dataTimeStamp+\"','\"+productC+\"','\"+articleCodeC+\"','\"+revisionC+\"','\"+statusC+\"','\"+readCodeC+\"','\"+measuredTrayPosition+\"','\"+x+\"','\"+y+\"','\"+z+\"','\"+trayDeltaC+\"','\"+x1+\"','\"+y1+\"','\"+z1+\"','\"+productUB+\"','\"+statusUB+\"','\"+productEC+\"','\"+statusEC+\"','\"+numberEC+\"','\"+articleCodeT+\"','\"+statusT+\"','\"+productT+\"','\"+trayCodeT+\"','\"+cmdDispense1+\"','\"+dispenseTime1+\"','\"+pressureSetPoint1+\"','\"+cmdDispense2+\"','\"+dispenseTime2+\"','\"+pressureSetPoint2+\"','\"+productG+\"','\"+statusG+\"','\"+productCup+\"','\"+articleCodeCup+\"','\"+revisionCup+\"','\"+statusCup+\"','\"+airPressureCup+\"','\"+airBackPressureCup+\"','\"+productOR+\"','\"+articleCodeOR+\"','\"+revisionOR+\"','\"+statusOR+\"','\"+minGreyValue+\"','\"+maxGreyValue+\"','\"+avgGreyScale+\"','\"+lowestGreyScale+\"','\"+highestGreyScale+\"','\"+stdDevGreyScale+\"','\"+batchCode+\"')\";\nmsg1.payload=stringy;\nreturn msg1;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1580,"y":340,"wires":[["069fcebe363d78b6"]]},{"id":"e8b12fa73d993e9f","type":"inject","z":"c876e57260edd2ab","name":"good data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"->\":{\"product\":{\"value\":\"ATM\"},\"articleCode\":{\"value\":\"4006110801\"},\"revision\":{\"value\":\"003\"},\"recipe\":{\"value\":\"4006110801 ATM DN25 FKM\"},\"recipeRevision\":{\"value\":\"001\"},\"serialNumber\":{\"value\":\"22815022\"},\"orderNumber\":{\"value\":\"dn25fkm 6-10-2022\"},\"labelCode\":{\"value\":\"4006110801_003_22815022_X\"},\"readCode\":{\"value\":\"4006110801_003_22815022_X\"},\"status\":{\"value\":-17},\"solderingStation\":{\"value\":\"4006110801_003_22815021_X\",\"->\":{\"solderTime\":{\"value\":4000},\"cooldownTime\":{\"value\":2000},\"power\":{\"value\":99}}},\"weld\":{\"value\":\"003\",\"->\":{\"rotatorStartPosition\":{\"value\":4},\"rotatorDegToWeld\":{\"value\":-7},\"rotatorWeldSpeed\":{\"value\":31.8},\"laserPowerWatt\":{\"value\":600},\"laserStartDelay\":{\"value\":10},\"clampPressure\":{\"value\":2.5}}},\"testResult\":{\"value\":-1017},\"pressurize\":{\"->\":{\"setPressure\":{\"value\":24},\"allowedError\":{\"value\":5},\"stabilisationTime\":{\"value\":1000},\"isolationTime\":{\"value\":15000},\"flowTime\":{\"value\":10000},\"maxTime\":{\"value\":5000},\"returnCode\":{\"value\":2},\"isolatedPressure\":{\"value\":24.216668}}},\"pressureDropTest\":{\"->\":{\"testTime\":{\"value\":30000},\"allowedStartError\":{\"value\":5},\"setTestPressure\":{\"value\":24},\"allowedDrop\":{\"value\":2.5},\"returnCode\":{\"value\":2},\"startPressure\":{\"value\":24.216101},\"endPressure\":{\"value\":23.8054},\"testResult\":{\"value\":2}}},\"impedanceTest\":{\"->\":{\"frequencies\":{\"value\":\"[20.000,2000.000]\"},\"voltageLevel\":{\"value\":0.1},\"minImpedances\":{\"value\":\"[3000.000,0.000]\"},\"maxImpedances\":{\"value\":\"[25000.000,100000.000]\"},\"minPhaseAngle\":{\"value\":\"[-90.000,-15.000]\"},\"maxPhaseAngle\":{\"value\":\"[0.000,0.000]\"},\"requiredSerialNumber\":{\"value\":\"\"},\"returnCode\":{\"value\":2},\"testResult\":{\"value\":-1000},\"leftImpedances\":{\"value\":\"[334099.000,9472.840]\"},\"leftPhaseAngle\":{\"value\":\"[-66.765,-40.662]\"},\"rightImpedances\":{\"value\":\"[35123.099,6916.809]\"},\"rightPhaseAngle\":{\"value\":\"[-47.940,-9.921]\"},\"actualSerialNumber\":{\"value\":\"GER880566\"}}},\"zeroFlowTest\":{\"->\":{\"numberOfSamples\":{\"value\":50},\"ticksPerSample\":{\"value\":25},\"dutSerialNumber\":{\"value\":\"TESTATM01\"},\"minDutStdDev\":{\"value\":0},\"maxDutStdDev\":{\"value\":0.008},\"minDutAverage\":{\"value\":-0.002},\"maxDutAverage\":{\"value\":0.002},\"returnCode\":{\"value\":2},\"testResult\":{\"value\":-1003},\"dutSensorStatus\":{\"value\":100512},\"standardDeviationDut\":{\"value\":0.002297},\"runningAverageDut\":{\"value\":0.002623},\"flowSource\":{\"value\":\"ATMObj1200\"},\"samplesTaken\":{\"value\":50},\"sampleTime\":{\"value\":12860},\"refSensorStatus\":{\"value\":0}}},\"setFlow\":{\"->\":{\"stabilisationTime\":{\"value\":8000}}},\"flowTest\":{\"->\":{\"numberOfSamples\":{\"value\":50},\"ticksPerSample\":{\"value\":25},\"dutSerialNumber\":{\"value\":\"TESTATM01\"},\"refSerialNumber\":{\"value\":\"20180612_30\"},\"minDutAverageDeviation\":{\"value\":-15},\"maxDutAverageDeviation\":{\"value\":10},\"minRefAverage\":{\"value\":0.35},\"maxRefAverage\":{\"value\":0.45},\"minDutStdDev\":{\"value\":0.0001},\"maxDutStdDev\":{\"value\":0.02},\"minRefStdDev\":{\"value\":0.0001},\"maxRefStdDev\":{\"value\":0.01},\"maxDutTemperature\":{\"value\":999999999999.9},\"minDutTemperature\":{\"value\":-999999999999.9},\"sizeFactor\":{\"value\":4.4685},\"returnCode\":{\"value\":2},\"testResult\":{\"value\":2},\"standardDeviationDut\":{\"value\":0.008876},\"standardDeviationRef\":{\"value\":0.003016},\"temperatureDut\":{\"value\":-55},\"temperatureRef\":{\"value\":25.457718},\"runningAverageDut\":{\"value\":0.384005},\"runningAverageRef\":{\"value\":0.415854},\"sampleTime\":{\"value\":16280},\"flowSource\":{\"value\":\"ATMObj1200\"},\"samplesTaken\":{\"value\":50}}},\"dataTimeStamp\":{\"value\":\"2022-10-06T06:43:20.916Z\"}},\"/\":{\"coil\":{\"->\":{\"product\":{\"value\":\"Coil\"},\"articleCode\":{\"value\":\"4008043701\"},\"revision\":{\"value\":\"000\"},\"status\":{\"value\":4},\"readCode\":{\"value\":\"4008043701 000 2209 38973 60@ 1600\"},\"measuredTrayPosition\":{\"value\":1592,\"->\":{\"x\":{\"value\":126},\"y\":{\"value\":0},\"z\":{\"value\":0}}},\"trayDelta\":{\"value\":225,\"->\":{\"x\":{\"value\":2.377136},\"y\":{\"value\":-1.259794},\"rz\":{\"value\":0.434165}}}},\"/\":{\"uBracket\":{\"->\":{\"product\":{\"value\":\"Ubracket\"},\"status\":{\"value\":1}}}}},\"eCable\":{\"->\":{\"product\":{\"value\":\"Ecable\"},\"status\":{\"value\":2},\"number\":{\"value\":18}}},\"tube\":{\"->\":{\"articleCode\":{\"value\":\"4005945302\"},\"status\":{\"value\":6},\"product\":{\"value\":\"Tube\"},\"trayCode\":{\"value\":\"00434 4005945202 003\"},\"rawAlignPosition\":{\"value\":930},\"maxAlignPosition\":{\"value\":950},\"minAlignPosition\":{\"value\":925},\"rawPressPosition\":{\"value\":967},\"maxPressPosition\":{\"value\":970},\"minPressPosition\":{\"value\":960},\"cmdDispense1\":{\"value\":970,\"->\":{\"dispenseTime\":{\"value\":40},\"pressureSetPoint\":{\"value\":4.1}}},\"cmdDispense2\":{\"value\":\"2022-10-06T06:35:16.676Z\",\"->\":{\"dispenseTime\":{\"value\":45},\"pressureSetPoint\":{\"value\":4.1}}}},\"/\":{\"Grease\":{\"->\":{\"product\":{\"value\":\"Grease\"},\"status\":{\"value\":1}}},\"cup\":{\"->\":{\"product\":{\"value\":\"Temperature insert\"},\"articleCode\":{\"value\":\"\"},\"revision\":{\"value\":\"\"},\"status\":{\"value\":2},\"airPressure\":{\"value\":5},\"airBackPressure\":{\"value\":2.5}},\"/\":{\"oRing\":{\"->\":{\"product\":{\"value\":\"oRing\"},\"articleCode\":{\"value\":\"060018V8836S190\"},\"revision\":{\"value\":\"000\"},\"status\":{\"value\":2},\"minGreyValue\":{\"value\":90},\"maxGreyValue\":{\"value\":225},\"avgGreyScale\":{\"value\":128},\"lowestGreyScale\":{\"value\":79},\"highestGreyScale\":{\"value\":220},\"stdDevGreyScale\":{\"value\":1581},\"batchCode\":{\"value\":\"0093561\"}}}}}}}}}","payloadType":"json","x":1400,"y":320,"wires":[["9a6094d969c93867"]]},{"id":"e60f233ed2b5d584","type":"inject","z":"c876e57260edd2ab","name":"bad data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"->\":{\"product\":{\"value\":\"ATM\"},\"articleCode\":{\"value\":\"4006110801\"},\"revision\":{\"value\":\"003\"},\"recipe\":{\"value\":\"4006110801 ATM DN25 FKM\"},\"recipeRevision\":{\"value\":\"001\"},\"serialNumber\":{\"value\":\"22815021\"},\"orderNumber\":{\"value\":\"dn25fkm 6-10-2022\"},\"labelCode\":{\"value\":\"4006110801_003_22815021_X\"},\"readCode\":{\"value\":\"QG92\"},\"status\":{\"value\":-4},\"dataTimeStamp\":{\"value\":\"2022-10-06T06:35:16.676Z\"}},\"/\":{\"coil\":{\"->\":{\"product\":{\"value\":\"Coil\"},\"articleCode\":{\"value\":\"4008043701\"},\"revision\":{\"value\":\"000\"},\"status\":{\"value\":4},\"readCode\":{\"value\":\"4008043701 000 2209 38975 60@ 1600\"},\"measuredTrayPosition\":{\"value\":1612,\"->\":{\"x\":{\"value\":63},\"y\":{\"value\":0},\"z\":{\"value\":0}}},\"trayDelta\":{\"value\":225,\"->\":{\"x\":{\"value\":2.21402},\"y\":{\"value\":-0.914638},\"rz\":{\"value\":0.491452}}}}}}}","payloadType":"json","x":1400,"y":360,"wires":[["9a6094d969c93867"]]},{"id":"069fcebe363d78b6","type":"debug","z":"c876e57260edd2ab","name":"debug 99","active":true,"tosidebar":true,"console":true,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1630,"y":400,"wires":[]}]


Question:

Do you have any control over the source data? The data format is (IMO) just terrible. All this -> and / in the property names is just not great!




Suggestion

When you want help from a forum member, it is far better to make a simple flow (like I did above) with demo data provided by an inject button that demonstrates the issue cleanly.
Users simply do not have access to your database or your MQTT broker - so including them in your flow is pointless and off-putting for most.

2 Likes

Thanks! This worked for me.

For your question - Unfortunately i do not have control over this. Maybe in the future but not at the moment. I will take your suggestion into account.

For your suggestion - Thanks will also keep it in mind.

Thanks for your help!

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.