Parsing JSON webhook trigger

[btobiczyk]

Have a webhook that is being called with a json payload that after the webhook module parses it outputs an array like so. as you can see from the json, the way it is parsing into xml makes sense but i need each “data” node in the array to be its own record coming out of the trigger. short of custom java, do you have a good way to parse the array natively?

<root>
	<row>
		<timestamp><![CDATA[1747322960749]]></timestamp>
		<field>
			<name><![CDATA[data[0][currentPeriod]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][estimatedProfitPercent]]]></name>
			<value><![CDATA[13]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][jobCloseDate]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][jobClose]]]></name>
			<value><![CDATA[false]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][projectId]]]></name>
			<value><![CDATA[0103-24-2093]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][prospectiveContractAmt]]]></name>
			<value><![CDATA[165413.63]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][currentValues][totalProjectedLaborHours]]]></name>
			<value><![CDATA[0]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][estimates][0][PJESTIMATEID]]]></name>
			<value><![CDATA[0103-24-2093-E]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][estimates][0][PROJECTID]]]></name>
			<value><![CDATA[0103-24-2093]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][estimates][0][TOTAL]]]></name>
			<value><![CDATA[127029.75]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][estimatedProfitPercent]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][jobCloseDate]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][jobClose]]]></name>
			<value><![CDATA[true]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][projectId]]]></name>
			<value><![CDATA[0103-24-2093]]></value>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][prospectiveContractAmt]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[0][updateValues][totalProjectedLaborHours]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[1][currentPeriod]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][estimatedProfitPercent]]]></name>
			<value><![CDATA[18]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][jobCloseDate]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][jobClose]]]></name>
			<value><![CDATA[false]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][projectId]]]></name>
			<value><![CDATA[0103-24-2102]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][prospectiveContractAmt]]]></name>
			<value><![CDATA[13725.07]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][currentValues][totalProjectedLaborHours]]]></name>
			<value><![CDATA[0]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][estimates][0][PJESTIMATEID]]]></name>
			<value><![CDATA[0103-24-2102-E]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][estimates][0][PROJECTID]]]></name>
			<value><![CDATA[0103-24-2102]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][estimates][0][TOTAL]]]></name>
			<value><![CDATA[4657.9]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][estimatedProfitPercent]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][jobCloseDate]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][jobClose]]]></name>
			<value><![CDATA[true]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][projectId]]]></name>
			<value><![CDATA[0103-24-2102]]></value>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][prospectiveContractAmt]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[1][updateValues][totalProjectedLaborHours]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[2][currentPeriod]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][estimatedProfitPercent]]]></name>
			<value><![CDATA[20]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][jobCloseDate]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][jobClose]]]></name>
			<value><![CDATA[false]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][projectId]]]></name>
			<value><![CDATA[0103-24-2073]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][prospectiveContractAmt]]]></name>
			<value><![CDATA[140000]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][currentValues][totalProjectedLaborHours]]]></name>
			<value><![CDATA[500]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][estimates][0][PJESTIMATEID]]]></name>
			<value><![CDATA[0103-24-2073-E]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][estimates][0][PROJECTID]]]></name>
			<value><![CDATA[0103-24-2073]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][estimates][0][TOTAL]]]></name>
			<value><![CDATA[112000]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][customerId]]]></name>
			<value><![CDATA[GENESIS001]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][departmentId]]]></name>
			<value><![CDATA[01-03]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][estimatedProfitPercent]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][jobCloseDate]]]></name>
			<value><![CDATA[Tue Dec 31 2024 00:00:00 GMT-0700 (Mountain Standard Time)]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][jobClose]]]></name>
			<value><![CDATA[true]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][locationId]]]></name>
			<value><![CDATA[01]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][projectId]]]></name>
			<value><![CDATA[0103-24-2073]]></value>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][prospectiveContractAmt]]]></name>
			<value/>
		</field>
		<field>
			<name><![CDATA[data[2][updateValues][totalProjectedLaborHours]]]></name>
			<value/>
		</field>
	</row>
</root>

{
    "data": [
        {
            "currentValues": {
                "projectId": "0103-24-2093",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": 165413.63,
                "totalProjectedLaborHours": 0,
                "estimatedProfitPercent": 13,
                "jobClose": false,
                "jobCloseDate": null
            },
            "updateValues": {
                "projectId": "0103-24-2093",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": null,
                "totalProjectedLaborHours": null,
                "estimatedProfitPercent": null,
                "jobClose": true,
                "jobCloseDate": "2024-12-31T07:00:00.000Z"
            },
            "estimates": [
                {
                    "PJESTIMATEID": "0103-24-2093-E",
                    "PROJECTID": "0103-24-2093",
                    "TOTAL": "127029.75"
                }
            ],
            "currentPeriod": "2024-12-31T07:00:00.000Z"
        },
        {
            "currentValues": {
                "projectId": "0103-24-2102",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": 13725.07,
                "totalProjectedLaborHours": 0,
                "estimatedProfitPercent": 18,
                "jobClose": false,
                "jobCloseDate": null
            },
            "updateValues": {
                "projectId": "0103-24-2102",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": null,
                "totalProjectedLaborHours": null,
                "estimatedProfitPercent": null,
                "jobClose": true,
                "jobCloseDate": "2024-12-31T07:00:00.000Z"
            },
            "estimates": [
                {
                    "PJESTIMATEID": "0103-24-2102-E",
                    "PROJECTID": "0103-24-2102",
                    "TOTAL": "4657.9"
                }
            ],
            "currentPeriod": "2024-12-31T07:00:00.000Z"
        },
        {
            "currentValues": {
                "projectId": "0103-24-2073",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": 140000,
                "totalProjectedLaborHours": 500,
                "estimatedProfitPercent": 20,
                "jobClose": false,
                "jobCloseDate": null
            },
            "updateValues": {
                "projectId": "0103-24-2073",
                "locationId": "01",
                "departmentId": "01-03",
                "customerId": "GENESIS001",
                "prospectiveContractAmt": null,
                "totalProjectedLaborHours": null,
                "estimatedProfitPercent": null,
                "jobClose": true,
                "jobCloseDate": "2024-12-31T07:00:00.000Z"
            },
            "estimates": [
                {
                    "PJESTIMATEID": "0103-24-2073-E",
                    "PROJECTID": "0103-24-2073",
                    "TOTAL": "112000"
                }
            ],
            "currentPeriod": "2024-12-31T07:00:00.000Z"
        }
    ]
}```

[robert]

I think you can take the raw JSON payload from the webook module’s output and feed it into the Data Streams - Create from Text module to get a better XML schema:

For your sample data it outputs this:

<o>
	<data>
		<e>
			<currentPeriod><![CDATA[2024-12-31T07:00:00.000Z]]></currentPeriod>
			<currentValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent><![CDATA[13]]></estimatedProfitPercent>
				<jobClose><![CDATA[false]]></jobClose>
				<jobCloseDate json_null="true"/>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2093]]></projectId>
				<prospectiveContractAmt><![CDATA[165413.63]]></prospectiveContractAmt>
				<totalProjectedLaborHours><![CDATA[0]]></totalProjectedLaborHours>
			</currentValues>
			<estimates>
				<e>
					<PJESTIMATEID><![CDATA[0103-24-2093-E]]></PJESTIMATEID>
					<PROJECTID><![CDATA[0103-24-2093]]></PROJECTID>
					<TOTAL><![CDATA[127029.75]]></TOTAL>
				</e>
			</estimates>
			<updateValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent json_null="true"/>
				<jobClose><![CDATA[true]]></jobClose>
				<jobCloseDate><![CDATA[2024-12-31T07:00:00.000Z]]></jobCloseDate>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2093]]></projectId>
				<prospectiveContractAmt json_null="true"/>
				<totalProjectedLaborHours json_null="true"/>
			</updateValues>
		</e>
		<e>
			<currentPeriod><![CDATA[2024-12-31T07:00:00.000Z]]></currentPeriod>
			<currentValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent><![CDATA[18]]></estimatedProfitPercent>
				<jobClose><![CDATA[false]]></jobClose>
				<jobCloseDate json_null="true"/>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2102]]></projectId>
				<prospectiveContractAmt><![CDATA[13725.07]]></prospectiveContractAmt>
				<totalProjectedLaborHours><![CDATA[0]]></totalProjectedLaborHours>
			</currentValues>
			<estimates>
				<e>
					<PJESTIMATEID><![CDATA[0103-24-2102-E]]></PJESTIMATEID>
					<PROJECTID><![CDATA[0103-24-2102]]></PROJECTID>
					<TOTAL><![CDATA[4657.9]]></TOTAL>
				</e>
			</estimates>
			<updateValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent json_null="true"/>
				<jobClose><![CDATA[true]]></jobClose>
				<jobCloseDate><![CDATA[2024-12-31T07:00:00.000Z]]></jobCloseDate>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2102]]></projectId>
				<prospectiveContractAmt json_null="true"/>
				<totalProjectedLaborHours json_null="true"/>
			</updateValues>
		</e>
		<e>
			<currentPeriod><![CDATA[2024-12-31T07:00:00.000Z]]></currentPeriod>
			<currentValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent><![CDATA[20]]></estimatedProfitPercent>
				<jobClose><![CDATA[false]]></jobClose>
				<jobCloseDate json_null="true"/>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2073]]></projectId>
				<prospectiveContractAmt><![CDATA[140000]]></prospectiveContractAmt>
				<totalProjectedLaborHours><![CDATA[500]]></totalProjectedLaborHours>
			</currentValues>
			<estimates>
				<e>
					<PJESTIMATEID><![CDATA[0103-24-2073-E]]></PJESTIMATEID>
					<PROJECTID><![CDATA[0103-24-2073]]></PROJECTID>
					<TOTAL><![CDATA[112000]]></TOTAL>
				</e>
			</estimates>
			<updateValues>
				<customerId><![CDATA[GENESIS001]]></customerId>
				<departmentId><![CDATA[01-03]]></departmentId>
				<estimatedProfitPercent json_null="true"/>
				<jobClose><![CDATA[true]]></jobClose>
				<jobCloseDate><![CDATA[2024-12-31T07:00:00.000Z]]></jobCloseDate>
				<locationId><![CDATA[01]]></locationId>
				<projectId><![CDATA[0103-24-2073]]></projectId>
				<prospectiveContractAmt json_null="true"/>
				<totalProjectedLaborHours json_null="true"/>
			</updateValues>
		</e>
	</data>
</o>

[btobiczyk]

is there a config to do that? when i tested it auto parsed it, i never saw the raw json.

Automation Editor

[robert]

Ah, they are sending POST parameters instead of a JSON or XML payload, which explains why you don’t see a raw payload emitted by the webhook module:

So the only way to reformat the data I can think of will be via custom JSP Java.

[btobiczyk]

they swear it is being sent in the body as it would be too long in a parameter.

[robert]

The system’s webhook log shows no JSON or XML payload, just POST parameters:

[btobiczyk]

so we did some more testing and trying different things. Have a possible hypothesis. He confirmed they are sending it as a body BUT for some reason if he includes a content type the call errors on his end (not sure why but it does). so we sent it without and i can see the full body but it is coming in as a parameter. im thinking that apiant possibly sees the data but no content type and assumes its a parameter?

see record below

Automation Editor

[robert]

Check the trigger’s log to view what they are sending. They are still not sending a JSON or XML payload, it is now seemingly received as a single POST body parameter

The content-type header should be application/json and they need to send it as the payload, not as POST body parameters