Question

Commas Prevent Running Line Itemizer on a Nested JSON Object

  • 21 January 2023
  • 8 replies
  • 345 views

Trigger: PhantomBuster LinkedIn Profile Scraper (Zapier integration, not a Webhook)

Desired Action: Insert fields from a nested JSON object into the next step

Problem: I can’t access data in the nested “jobs” array the same way I can in the first-level “resultObject” array show below:

Specifically, the output I’d like to achieve would look like this:

Vice President, Operations
ABC Company
Jun 2020 - Present

Director, Operations
XYZ Company
Jan 2015 - Jun 2020

etc. 

I’ve spent hours pouring over Zapier documentation and this forum and have tried many things none of which work:

  • Line itemizer (format utility): 
    comes very very close to doing what I need here, except for this incredibly painful problem with commas inside the line items. Any/all of the fields inside the jobs array could contain commas and completely break everything. 
  • Code block
    to parse a nested JSON object -- I don’t see a way to pass the output from PhantomBuster into the code block to be parsed, but even if I did … I pasted in a full sample output, and it still didn’t work (yes, I verified that the JSON is valid)
  • Loop
    I looped through the line items inside the jobs object and ran into the same problem with commas

Please save me from this solution that I assume would work, but … really?

  1. Replace instances of “, “ (including space) in all the line items with a weird string like &&& or something, which I believe will take one task per field
  2. Then run the line itemizer so it works properly
  3. Then undo step 1 to reformat the result replacing the &&& with “, “

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

8 replies

Userlevel 7
Badge +14

Hi @wafflessun 

Good question.

Generally nested arrays have to be handled in a Code step.

Cool. If you know of any examples I could study, let me know.

My triggers is coming from a native integration and I don’t have access to the original JSON. This idea to rebuild it doesn’t seem to work for nested JSON objects. And this idea shows some code that might work but assumes you have easy access to the original JSON payload which I do not. 

Userlevel 7
Badge +14

@wafflessun 

Here’s a trick…

Use 2 Zaps…

 

Zap 1

  1. Trigger:
  2. Action: Webhooks - POST
    1. Only set the webhook URL from Zap 2

 

Zap 2

  1. Trigger: Webhook - Catch Hook
    1. This will generate a unique webhook URL to put in Zap 1, Step 2
    2. This will receive the nested raw JSON
  2. Action: Code

Ok, here is what I learned and what I ended up doing which works for my needs:

Disclaimer: I’m no developer but I play one inside Zapier. My javascript below is not elegant, probably very inefficient, but it’s valid and it works. If you have suggestions on how it could be better, I’m all ears!

Learnings

  • Zapier appears to treat nested JSON objects as text blobs, not as line items. This means the line items to text utility won’t work the way it would if the JSON object I’m trying to map was not nested. 
    • That means the first part of this solution didn’t work for me, but I used the second part as shown below. 
  • The text to line items utility (and then presumably back to text?) will also not work in my case because my values have commas inside them not just between them. (as described here)
  • There are many solutions in the community that assume you have access to the raw webhook. I can send a webhook from PhantomBuster but the raw webhook doesn’t give me the full payload inside Zapier. Maybe this is user error, but it looked like a dead end to me. 
  • Zapier steps are “expensive.” :) Maybe there is a way to do what I’ve done in a simpler way using multiple Zapier steps, but this gets the job done in a single code step. 

Warning and Help Request

This only works because the commas I typically see inside values have a space after them. This allows me to distinguish them from the commas that separate the values. But I suppose it’s possible there could be a comma inside a value that doesn’t have a space after it. If/when that happens, I can’t think of any way to fix this. If you can, let me know!

General approach in English

  1. Feed in the text elements into Zapier. In my case: title, company, dates, geography.
  2. Prepare each text element by a) replacing the internal commas (with spaces after them) with a ridiculous string we can replace later and b) splitting each into an array on the remaining commas. (which don’t have spaces after them).
  3. Create new array(s) using the component arrays (in my case two arrays, one for current jobs and one for past jobs). 
  4. Put the two arrays together in a final array with current first followed by past. 
  5. Replace all instances of the ridiculous string we inserted earlier with real commas. 
  6. Return an object to Zapier with mappable name/value pairs (this is where I used the second part of this solution.)

The Output (Yay)

The Code

//turn values in nested JSON objects into mappable Zapier values

//set up variables
let currentNum = 0; // set up increment
let pastNum = 0; // set up increment
let currentArray = "";
let pastArray = "";
let delimiter1 = "***"; // separates keys and values
let delimiter2 = "|||"; // separates key value pairs from each other
let replacer = "%%%"; // weird string to find and replace
let commas = ", "; // comma pattern to match

// turn Zapier text inputs into clean arrays to loop through
const title = prepare(inputData.title);
const company = prepare(inputData.company);
const dates = prepare(inputData.dates);
const geography = prepare(inputData.geography);

// create new arrays by interleaving values in the arrays we just created
// assume titles is the longest array (no jobs without titles)
for (let i = 0; i < title.length ; i++) {
if (dates[i].match(/present/i)) { // i = regardless of case
currentNum++;
currentArray = currentArray +
job (title[i],company[i],dates[i],geography[i],currentNum,"current");
} else {
pastNum++;
pastArray = pastArray +
job (title[i],company[i],dates[i],geography[i],pastNum,"past");
}
}

// put the two arrays together in a single array with current first
let masterArray = currentArray + pastArray;

// replace the absurd string we put in earlier with real commas
const regex = new RegExp(replacer,'gi'); // gi finds all regardless of case
masterArray = masterArray.replace(regex,commas);

// create name value pairs in an object Zapier can parse
const jobs = masterArray.split(delimiter2);
let obj = {};
jobs.forEach((element) => {
const [name, value] = element.split(delimiter1);
obj[name] = value;
});

return [obj];

// turn comma-delimited text into an array we can parse
function prepare(input) {
// make sure the value is defined because some are blank
if (typeof input !== "undefined") {
// replace commas inside each value with an absurd string
const regex = new RegExp(commas,'gi'); //gi finds all instances
input = input.replace(regex, replacer);
// split the text into an array on the remaining commas
return input.split(",");
}
return;
}

function job(title,company,dates,geography,num,prefix) {
let job =
prefix + "Title" + num + delimiter1 + title + delimiter2 +
prefix + "Company" + num + delimiter1 + company + delimiter2 +
prefix + "Dates" + num + delimiter1 + dates + delimiter2 +
prefix + "Location" + num + delimiter1 + geography + delimiter2
;
return job;
}

 

@Troy Tessalone I tried what I think you were suggesting. I got the webhooks to talk to each other, but all I got was the same flat text values that I always see. If there’s a way to pull these in as true line items, then I don’t have to worry about the use case where there’s a comma without a subsequent space described in my solution above. 

What am I doing wrong here?

My Zap 1 (trying to follow your directions)

My Zap 2 (trying to follow your directions)

 

Userlevel 7
Badge +14

@wafflessun 

Try changing the Zap 2 trigger to this:

 

Yep, thank you. Got the raw body now but still having a devil of a time accessing the jobs array from this structure. Any help appreciated.

 

 

Userlevel 7
Badge +14

@wafflessun

Try asking ChatGPT by OpenAI for the JavaScript code to parse the desired data from the JSON: https://chat.openai.com