Best answer

Accessing nested JSON array data in Zapier Webhook

  • 10 February 2020
  • 16 replies
  • 19241 views

Userlevel 1

How can we access nested JSON array elements in a Zapier Webhook?


Given this incoming JSON structure:


lead_id

user_column_data:[

{column_name:User Email,

string_value:test@example.com,

column_id:EMAIL},

{column_name:First Name,

string_value:FirstName,

column_id:FIRST_NAME},

{column_name:Last Name,

string_value:LastName,

column_id:LAST_NAME},

{column_name:User Phone,

string_value:+16505550123,

column_id:PHONE_NUMBER},

{column_name:Region,

string_value:California,

column_id:REGION}

],

api_version:1.0,

form_id:6849587745,

campaign_id:6544203960,

google_key:J2wp6st683bfTQRcD8l6v36LLfeaoV9fHoHtNRC0HB7tr90PiG,

is_test:true,

adgroup_id:0,

creative_id:0


... we need to access these elements:


lead_id

Email

First Name

Last Name

User Phone

Region

campaign_id

adgroup_id

creative_id

form_id

api_version



I tried wiring up these paths:


lead_id

{{user_column_data[1].string_value}}

{{user_column_data[2].string_value}}

{{user_column_data[3].string_value}}

{{user_column_data[4].string_value}}

{{user_column_data[5].string_value}}

campaign_id

adgroup_id

creative_id

form_id

api_version



... and we do get the outer elements (lead_id, campaign_id, adgroup_id, etc.), but I'm not able to access any individual JSON elements from the array, e.g.:


{{user_column_data[1].string_value}}


... which should represent the email address.


Am I missing something obvious here? I couldn't find any definitive information in your support system for JSON arrays.




icon

Best answer by jesse 11 February 2020, 18:18

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

16 replies

Userlevel 4
Badge +4

I have gotten full access to the JSON array through the following steps:

1) Catch a raw webhook

2) Send the webhook data to a Code by Zapier step

3) Start with the following code and output the values you want to use.

var obj = {},

data = JSON.parse(inputData.data); //Data will now function like a json object


//Pull out your values

obj.value = data.user_column_data[1].string_value

return obj;


Userlevel 7
Badge +9

Hey friends! Just wanted to offer some additional context here,

Generally, the Webhooks trigger doesn't do well in handling nested arrays so you won't be able to grab both the array as well as the rest of the payload in the trigger unless you're using a Code step workaround like @BowTieBots suggested.

Another option here is to create a Developer App using our Web builder that'll give you a lot more customization with handling that incoming payload.

If you have any other questions with this payload, definitely continue to ask here in the thread or category and our Community members are likely to offer some suggestions and tips for you!



I have gotten full access to the JSON array through the following steps:

1) Catch a raw webhook

2) Send the webhook data to a Code by Zapier step

3) Start with the following code and output the values you want to use.

var obj = {},

data = JSON.parse(inputData.data); //Data will now function like a json object

 

//Pull out your values

obj.value = data.user_column_data[1].string_value

return obj;

 

Hi @BowTieBots  - Thanks for this. I am having a similar problem, but unfortunately I am not technical at all. I have followed the steps in this post, created a raw webhook and inserted the code, but getting an error message - cannot read property [1] of undifined. I have named the data field ‘data’ and selected the raw webhook. Appreciate your advice.

Userlevel 4
Badge +4

Hi @arayes,

 

Your error is coming form this line: obj.value = data.user_column_data[1].string_value

The code assumes the property “user_column_data” exists in your JSON object.  It looks like that is not the case for your use.

 

Can you paste a copy of the incoming raw webhook and let me know what values you want to pull out I can help you fix the error.

 

 

Hi @arayes,

 

Your error is coming form this line: obj.value = data.user_column_data[1].string_value

The code assumes the property “user_column_data” exists in your JSON object.  It looks like that is not the case for your use.

 

Can you paste a copy of the incoming raw webhook and let me know what values you want to pull out I can help you fix the error.

 

Hi@BowTieBots 

Thanks so much for your prompt response. Kindly find below the raw data:

 

{"paymethod_id":1,"business_id":529,"delivery_datetime":"2020-03-31 17:02:00","driver_tip":0,
"pay_data":null,"driver_id":null,"status":0,"language_id":1,"app_id":"WEBSITE","delivery_zone_id":923,
"cash":null,"comment":null,"customer_id":null,"offer_id":null,"delivery_zone_price":0,"offer":0,"discount":0,
"service_fee":3,"tax_type":1,"tax":18,"to_print":false,"updated_at":"2020-03-31 15:02:56",
"created_at":"2020-03-31 15:02:56","id":8355,"customer":{"id":8355,"order_id":8355,"name":"a","photo":null,
"lastname":null,"email":"xxxxx@xxx.biz","dropdown_option_id":null,
"address":"xxxxxxx, \u0126al G\u0127arg\u0127ur, Mxxxaxx","address_notes":null,"zipcode":null,
"cellphone":"99999999","phone":null,"location":{"lat":35.9209037,"lng":14.453120000000016,
"from_google":false},"internal_number":null,"map_data":null,"tag":"other","middle_name":null,
"second_lastname":null,"country_phone_code":null,"dropdown_option":null},"business":{"id":8355,
"order_id":8355,"name":"xxx.Delivery","logo":
"https:\/\/res.cloudinary.com\/logicpass-ltd\/image\/upload\/v1585508566\/guk6401wf4cyvmvph2ij.png",
"email":"xxx@xxx.biz","city_id":62,"address":"xxxxxxxx",
"address_notes":null,"zipcode":null,"cellphone":null,"phone":null,"location":{"lat":35.8888045,
"lng":14.4752306,"zipcode":-1,"zoom":15},"city":{"id":62,"name":"xxxa","country_id":1,
"administrator_id":4,"enabled":true}},

"products":[
{"id":47326,"
product_id":64074,
"order_id":8355,
"name":"Fresh Lactose Free Milk - 0.5l",
"price":0,
"quantity":1,
"comment":null,
"ingredients":[],
"options":[],
"featured":false,
"upselling":false,
"in_offer":false,
"offer_price":null,
"images":null,
"category_id":5003,
"to_string":"1 x Fresh Lactose Free Milk - 0.5l: \u20ac0\n"}
],

"driver":null,
"paymethod":{"id":1,"name":"Cash","gateway":"cash","enabled":true,"deleted_at":null,
"created_at":"2018-10-06 18:18:25","updated_at":"2018-10-06 18:18:25"},"review":null,"attachments":[],
"metafields":[],"order_type":"Delivery","delivery_datetime_day":"Tuesday","delivery_date":"2020-03-31",
"delivery_time":"0","tax_type_description":"Tax included on price","offer_percentage":null,"status_name":
"Pending","owner_push_id_1":"04eba313-e0f0-4b8c-a060-70a2c22616e8","calculated":{"subtotal":0,"tax":0,
"delivery_fee":0,"driver_tip":0,"service_fee":0,"discount":0,"total":0}}

headers__http_host
hooks.zapier.com
headers__http_x_request_id
07634c0ba2f737ae3637b644040caf9d
headers__http_x_real_ip
52.5.156.175
headers__http_x_forwarded_for
52.5.156.175
headers__http_x_forwarded_host
hooks.zapier.com
headers__http_x_forwarded_port
443
headers__http_x_forwarded_proto
https
headers__http_x_scheme
https
headers__http_x_original_forwarded_for
52.5.156.175
headers__content_length
2334
headers__content_type
application/json
headers__http_user_agent
GuzzleHttp/6.5.1 curl/7.47.0 PHP/7.2.8-1+ubuntu16.04.1+deb.sury.org+1

 

It’s the ‘products’ section (in yellow) that I am interested in. I have replaced ‘user column data’ with ‘products’ and removed the [1] after it, to make the test run successfully….but data is still not there.

 

 

Userlevel 4
Badge +4

@arayes 

Try using:

obj.value = data.projects[0] that should return the full object.

 

A specific value like “product_id” would be:

obj.value = data.projects[0][“project_id”]

@arayes

Try using:

obj.value = data.projects[0] that should return the full object.

 

A specific value like “product_id” would be:

obj.value = data.projects[0][“project_id”]

@BowTieBots Thats great. It worked!...I have been trying to solve this for 2 days….you’re a legend.  

There is another issue, which will arise when I have orders with multiple products selected. I would need to extract all the details into seperate rows in a google sheet. this method allows me to collect only one item at a time. Are there any ways to solve this using code ?

Userlevel 4
Badge +4

Hi @arayes,

We can solve this for multiple rows.  Can you confirm this is your process right now:

 

Action: Create Spreadsheet Row(s) in Google Sheets.

Code output is automatically parsing out the fields in the JSON object to single variables.

Each line item is getting mapped to the correct column in Google Sheets.

Hi @arayes,

We can solve this for multiple rows.  Can you confirm this is your process right now:

 

Action: Create Spreadsheet Row(s) in Google Sheets.

Code output is automatically parsing out the fields in the JSON object to single variables.

Each line item is getting mapped to the correct column in Google Sheets.

Hi @BowTieBots  Yes, that would be correct

Userlevel 4
Badge +4

@arayes 

Ok, put this in your code step instead.  The code assumes that:

  1. The field labels in products do not change between triggers, and
  2. Every product has the same number of fields, even if they are zero or null

**This code won’t work for the ingredients and options values.  They will always be blank.

 

var obj = {},

data = JSON.parse(inputData.data); //Data will now function like a json object

for(var y in data.products[0]){

   obj[y] = [];

}

for(var x in data.products){

   for(var y in data.products[x]){

       obj[y].push(data.products[x][y]);

   }

}

return obj;

@arayes

Ok, put this in your code step instead.  The code assumes that:

  1. The field labels in products do not change between triggers, and
  2. Every product has the same number of fields, even if they are zero or null

**This code won’t work for the ingredients and options values.  They will always be blank.

 

var obj = {},

data = JSON.parse(inputData.data); //Data will now function like a json object

for(var y in data.products[0]){

   obj[y] = [];

}

for(var x in data.products){

   for(var y in data.products[x]){

       obj[y].push(data.products[x][y]);

   }

}

return obj;

 

@BowTieBots  This is great. I have tested it, and it seems to work exactly as it should, but I have now come to another issue. I can’t seem to find a way of getting the data that webhook seemed to initially pick out quite well, e.g. email, address, customer name etc etc...which I was able to obtain by doing the standard webhook operation (not raw data). Can I initiate two webhooks at the same time, one raw and the other normal ?

Userlevel 4
Badge +4

@arayes You can’t run both webhooks at the same time.

Try adding this line just above return obj;

 

obj.webhook = data;

 

That should give you back all the values you had from a normal webhook.

@arayes You can’t run both webhooks at the same time.

Try adding this line just above return obj;

 

obj.webhook = data;

 

That should give you back all the values you had from a normal webhook.

@BowTieBots - Thats brilliant. Thanks ever so much. Not sure how I would have coped without your support. I was just going round and round in circles.

@arayes You can’t run both webhooks at the same time.

Try adding this line just above return obj;

 

obj.webhook = data;

 

That should give you back all the values you had from a normal webhook.

@BowTieBots - Thats brilliant. Thanks ever so much. Not sure how I would have coped without your support. I was just going round and round in circles.

@BowTieBots - Hi again. A quick question, do you know whether its possible to add a new line item at the end of a XERO invoice - for delivery fee or service fee for example ?

 

Userlevel 4
Badge +4

@arayes Xero can do multiple line item invoice creation but you can not update an existing invoice.

Creating multiple lines works the same as Google Sheets

 

 

I’d like to have the formatted/parsed values form the CATH webhook (from JSON) and also the CATCH RAW DATA webhook (unparsed data + access to headers) at the same time.
What I’m forced to do right now is to use and the catch raw date, plug in in a code zap and then parse the JSON and return both parsed and unparsed data to have both. The two behavior exists and works but can’t be combined, having to use code feels wrong.


 

var parsedData = JSON.parse(inputData.rawData);

output = {"parsedDate": parsedData, "rawDate": inputData.rawData};

 

Unless it is intended to force multi-steps zap and so having a paid plan.