JSON data parsing not working

jtb · November 2, 2023, 3:47pm

Describe the problem/error/question

I have the following JSON code:

[
  {
    "headers": {
      "host": "automate.jtb.media",
      "x-forwarded-scheme": "https",
      "x-forwarded-proto": "https",
      "x-forwarded-for": "167.235.115.90",
      "x-real-ip": "167.235.115.90",
      "content-length": "1381",
      "accept": "*/*",
      "accept-encoding": "gzip",
      "content-type": "application/x-www-form-urlencoded"
    },
    "params": {},
    "query": {},
    "body": {
      "{\"action\":\"created\",\"event_start_datetime\":\"2023-11-03T10:00\",\"event_end_datetime\":\"2023-11-03T10:30\",\"delivery_method\":\"Come by our store\",\"appointment_date\":\"11-03-2023\",\"appointment_time\":\"10:00 AM\",\"appointment_number\":\"230810041\",\"appointment_status\":\"Pending\",\"created_date\":\"11-02-2023\",\"created_time\":\"15:38\",\"customer_name\":\"John Doe\",\"customer_email\":\"[email protected]\",\"customer_phone\":\"1234567890\",\"customer_house_no\":\"\",\"customer_street_address\":\"\",\"customer_city\":\"\",\"customer_zipcode\":\"\",\"customer_notes\":\"This is a test appointment\",\"category\":\"Smartphone\",\"brand\":\"Apple\",\"model\":\"iPhone 11 Pro Max\",\"color\":\"Space Gray\",\"shop_name\":\"Repair Shop\",\"location_name\":\"Shop 1\",\"location_telephone\":\"1234567890\",\"location_address\":\"123 Main St\",\"location_zipcode_city\":\"New York, NY 10001\",\"reschedule_link\":\"This will be available in original payload\",\"cancel_link\":\"This will be available in original payload\",\"items\":[{\"name\":\"LCD Display\",\"type\":\"repair\",\"price\":30},{\"name\":\"Screen - Official\",\"type\":\"repair\",\"price\":0},{\"name\":\"Wireless Charging Port\",\"type\":\"repair\",\"price\":50},{\"name\":\"Mobile Safety Glass US\",\"type\":\"upsale\",\"price\":19.95}],\"delivery_method_cost\":20,\"sub_total\":119.95,\"combo_discount\":10,\"coupon_code\":\"20OFFNOW\",\"coupon_type\":\"fixed\",\"coupon_amount\":20,\"tax_percentage\":21,\"excluding_tax_price\":74.34,\"tax_price\":15.61,\"total_price\":89.95}": ""
    }
  }
]

Somehow i can’t use the expressions function to get some of the data from the body part of the JSON code. Like it’s explained over the:

Expressions | n8n Docs

What is the error message (if any)?

Please share your workflow

Share the output returned by the last node

EmeraldHerald · November 2, 2023, 4:36pm

Hi @jtb Welcome to the community

You aren’t sending over valid JSON, which is why you’re having difficulty selecting it properly You can use the Edit Fields node like this to fix up that data:

From there you should be able to access the customer email pretty easily!

jtb · November 3, 2023, 9:57am

Thanks for your reply. Copied your code and tried but still get an error. Like this one:
ERROR: The entry “body” inside ‘Fields to Set’ in item 0 contains invalid JSON

jtb · November 6, 2023, 8:05am

Nobody who knows the solution?

Jon · November 6, 2023, 9:24am

Hey @jtb,

Can you share a screenshot of the error you are seeing?

jtb · November 6, 2023, 10:09am

This is the error.

Jon · November 6, 2023, 10:16am

Hey @jtb,

Your body data looks different to what you originally shared so you may need to make adjustments to work with the new data.

jtb · November 8, 2023, 6:59am

Hi,

Can you point me in the right direction? This is the data:

[
  {
    "headers": {
      "host": "automate.jtb.media",
      "x-forwarded-scheme": "https",
      "x-forwarded-proto": "https",
      "x-forwarded-for": "167.235.115.90",
      "x-real-ip": "167.235.115.90",
      "content-length": "1417",
      "accept": "*/*",
      "accept-encoding": "gzip",
      "content-type": "application/x-www-form-urlencoded"
    },
    "params": {},
    "query": {},
    "body": {
      "{\"action\":\"created\",\"event_start_datetime\":\"2023-11-08T09:00\",\"event_end_datetime\":\"2023-11-08T10:00\",\"delivery_method\":\"Come by our store\",\"appointment_date\":\"08-11-2023\",\"appointment_time\":\"09:00\",\"appointment_number\":\"231103012\",\"appointment_status\":\"Pending\",\"created_date\":\"03-11-2023\",\"created_time\":\"14:48:59\",\"customer_name\":\"XXXXXXXXXXXXX\",\"customer_email\":\"[email protected]\",\"customer_phone\":\"xxxxx\",\"customer_house_no\":null,\"customer_street_address\":null,\"customer_city\":null,\"customer_zipcode\":null,\"customer_notes\":\"Telefoon loopt snel leeg, batterij vervangen\",\"category\":\"Smartphone\",\"brand\":\"Nokia\",\"model\":\"XR20\",\"color\":\"Ultra Blue\",\"shop_name\":\"jtb-media\",\"location_name\":\"jtb-media\",\"location_telephone\":\"\",\"location_address\":\"Westerweiden 29\",\"location_zipcode_city\":\"7961 EA Ruinerwold\",\"reschedule_link\":\"https:\\/\\/jtb-media.nl\\/reparaties\\/?reschedule": "1",
      "o_id_fk": "10",
      "c_id": "10",
      "uid": "d3d94468",
      "selected": "finalize\\/b_id=10\\/model_name=XR20\\/color=Ultra Blue\\/u=\\/a=8116\\/r=1933\\/finalprice=0\\/coupon=0\\/subtotal=0\",\"cancel_link\":\"https:\\/\\/jtb-media.nl\\/reparaties\\/?selected=cancel",
      "id": "10",
      "secret_key": "1e48c4420b7073bc11916c6c1de226bb\",\"items\":[{\"name\":\"Batterij - Officieel\",\"type\":\"repair\",\"price\":0}],\"delivery_method_cost\":\"\",\"sub_total\":0,\"combo_discount\":\"\",\"coupon_code\":\"\",\"coupon_type\":\"\",\"coupon_amount\":\"\",\"tax_percentage\":\"21\",\"excluding_tax_price\":0,\"tax_price\":0,\"total_price\":0}"
    }
  }
]```

Jon · November 8, 2023, 4:17pm

Hey @jtb,

Is that the acutal data from the webhook node? Are you in control of the system sending the data as it may be better to configure that to send the data in a “correct” format as if we keep this up we will just be trying to come up with solutions to work around something that may change again.

jtb · November 9, 2023, 10:25am

This the is the data from the last two webhook nodes:


[
  {
  "headers": 
  {
  "host": 
  "automate.jtb.media",
  "x-forwarded-scheme": 
  "https",
  "x-forwarded-proto": 
  "https",
  "x-forwarded-for": 
  "167.235.115.90",
  "x-real-ip": 
  "167.235.115.90",
  "content-length": 
  "1471",
  "accept": 
  "*/*",
  "accept-encoding": 
  "gzip",
  "content-type": 
  "application/x-www-form-urlencoded"
  },
  "params": 
  {
  },
  "query": 
  {
  },
  "body": 
  {
  "{"action":"created","event_start_datetime":"2023-11-09T09:00","event_end_datetime":"2023-11-09T10:00","delivery_method":"Come by our store","appointment_date":"09-11-2023","appointment_time":"09:00","appointment_number":"231108111","appointment_status":"Pending","created_date":"08-11-2023","created_time":"12:57:06","customer_name":"Danique","customer_email":"[email protected]","customer_phone":"0621xx3061","customer_house_no":null,"customer_street_address":null,"customer_city":null,"customer_zipcode":null,"customer_notes":"Iemand anders komt hem brengen, kan ik hem dan zelf rond 18:15 weer ophalen?","category":"Smartphone","brand":"Apple","model":"iPhone SE (2020)","color":"Black","shop_name":"jtb-media","location_name":"jtb-media","location_telephone":"","location_address":"Westerweiden 29","location_zipcode_city":"7961 EA Ruinerwold","reschedule_link":"https:\/\/jtb-media.nl\/reparaties\/?reschedule": 
  "1",
  "o_id_fk": 
  "12",
  "c_id": 
  "12",
  "uid": 
  "c20ad4d7",
  "selected": 
  "finalize\/b_id=1\/model_name=iPhone SE (2020)\/color=Black\/u=\/a=7120\/r=1435\/finalprice=0\/coupon=0\/subtotal=0","cancel_link":"https:\/\/jtb-media.nl\/reparaties\/?selected=cancel",
  "id": 
  "12",
  "secret_key": 
  "a01610228fe998f515a72dd730294d87","items":[{"name":"Batterij - OEM (Geen batterijmelding)","type":"repair","price":0}],"delivery_method_cost":"","sub_total":0,"combo_discount":"","coupon_code":"","coupon_type":"","coupon_amount":"","tax_percentage":"21","excluding_tax_price":0,"tax_price":0,"total_price":0}"
  }
  }
  ]

Second one:


[
  {
  "headers": 
  {
  "host": 
  "automate.jtb.media",
  "x-forwarded-scheme": 
  "https",
  "x-forwarded-proto": 
  "https",
  "x-forwarded-for": 
  "167.235.115.90",
  "x-real-ip": 
  "167.235.115.90",
  "content-length": 
  "1417",
  "accept": 
  "*/*",
  "accept-encoding": 
  "gzip",
  "content-type": 
  "application/x-www-form-urlencoded"
  },
  "params": 
  {
  },
  "query": 
  {
  },
  "body": 
  {
  "{"action":"created","event_start_datetime":"2023-11-08T09:00","event_end_datetime":"2023-11-08T10:00","delivery_method":"Come by our store","appointment_date":"08-11-2023","appointment_time":"09:00","appointment_number":"231103012","appointment_status":"Pending","created_date":"03-11-2023","created_time":"14:48:59","customer_name":"untel","customer_email":"XXXX","customer_phone":"062836","customer_house_no":null,"customer_street_address":null,"customer_city":null,"customer_zipcode":null,"customer_notes":"Telefoon loopt snel leeg, batterij vervangen","category":"Smartphone","brand":"Nokia","model":"XR20","color":"Ultra Blue","shop_name":"jtb-media","location_name":"jtb-media","location_telephone":"","location_address":"Westerweiden 29","location_zipcode_city":"7961 EA Ruinerwold","reschedule_link":"https:\/\/jtb-media.nl\/reparaties\/?reschedule": 
  "1",
  "o_id_fk": 
  "10",
  "c_id": 
  "10",
  "uid": 
  "d3d94468",
  "selected": 
  "finalize\/b_id=10\/model_name=XR20\/color=Ultra Blue\/u=\/a=8116\/r=1933\/finalprice=0\/coupon=0\/subtotal=0","cancel_link":"https:\/\/jtb-media.nl\/reparaties\/?selected=cancel",
  "id": 
  "10",
  "secret_key": 
  "1e48c4420b7073bc11916c6c1de226bb","items":[{"name":"Batterij - Officieel","type":"repair","price":0}],"delivery_method_cost":"","sub_total":0,"combo_discount":"","coupon_code":"","coupon_type":"","coupon_amount":"","tax_percentage":"21","excluding_tax_price":0,"tax_price":0,"total_price":0}"
  }
  }
  ]

I’ve obfuscated the customer data.

The data is coming from a webplugin that i can’t change unfortunatly.

Thanks for your time and patience, i really appreciate that.

jtb · November 27, 2023, 10:09am

Does someone know the anwser?

Jon · November 27, 2023, 12:51pm

Hey @jtb,

To fix this you will need to either correct the plugin so it sends the data correctly or modify the previous examples to work with your data format. This may not be easy to do if the data format keeps changing though like it has during this topic.

jtb · November 27, 2023, 3:33pm

Hi Jon,

The latest two are the same right? It’s not changing anymore. So that would be the right format. How can i use it?

Jon · November 27, 2023, 4:40pm

Hey @jtb,

They look to be similar but it also looks like something is missing as the json is not valid, The issue is coming from the reschedule link after you have that there is a random 1 but also what looks to be a missing key name assuming that is the value.

This part below is the bit causing the issues, Does the 1 ever change and is it ever needed a possible solution could be to update the previous example to just remove that value with a replace but that could cause other issues.

"reschedule_link":"https:\/\/jtb-media.nl\/reparaties\/?reschedule": 
  "1",

I would strongly recommend fixing the plugin to send the data correctly unless you have the time available to work around this in code as I suspect there will be other issues in the future that we are not seeing now.

jtb · November 28, 2023, 2:09pm

Hi Jon,

I will contact the author of the plugin. I will get back to you soon.

Thanks for your help so far.

jtb · January 24, 2024, 7:49am

Hi Jon,

The plug-in has been updated. This is the new JSON data.

[

{

"headers": {

"host": "automate.jtb.media",

"x-forwarded-scheme": "https",

"x-forwarded-proto": "https",

"x-forwarded-for": "116.202.208.211",

"x-real-ip": "116.202.208.211",

"content-length": "1359",

"accept": "*/*",

"accept-encoding": "gzip",

"content-type": "application/x-www-form-urlencoded"

},

"params": {

},

"query": {

},

"body": {

"{"action":"created","event_start_datetime":"2024-01-25T11:00","event_end_datetime":"2024-01-25T12:00","delivery_method":"Come by our store","appointment_date":"25-01-2024","appointment_time":"11:00","appointment_number":"240124074","appointment_status":"Pending","created_date":"24-01-2024","created_time":"08:47:58","customer_name":"Jeroen","customer_email":"[email protected]","customer_phone":"0615577004","customer_house_no":null,"customer_street_address":null,"customer_city":null,"customer_zipcode":null,"customer_notes":"","category":"Smartphone","brand":"Apple","model":"iPhone 14","color":"Blue","shop_name":"jtb-media","location_name":"jtb-media","location_telephone":"","location_address":"Westerweiden 29","location_zipcode_city":"7961 EA Ruinerwold","reschedule_link":"https:\/\/jtb-media.nl\/reparaties\/?reschedule": "1",

"o_id_fk": "27",

"c_id": "27",

"uid": "02e74f10",

"selected": "finalize\/b_id=1\/model_name=iPhone 14\/color=Blue\/u=\/a=21\/r=5\/finalprice=0\/coupon=0\/subtotal=0","cancel_link":"https:\/\/jtb-media.nl\/reparaties\/?selected=cancel",

"id": "27",

"secret_key": "23fc4cba066f390a8cc729c7592b6ee8","items":[{"name":"Schermmodule - Officieel","type":"repair","price":0}],"delivery_method_cost":"","sub_total":0,"combo_discount":"","coupon_code":"","coupon_type":"","coupon_amount":"","tax_percentage":"21","excluding_tax_price":0,"tax_price":0,"total_price":0}"

}

}

]

Would it be possible now?

Jon · January 24, 2024, 1:52pm

Hey @jtb,

You can try using {{ JSON.parse( $json.body ) }} in an expression after the webhook node and see if that works.