Best Way to Traverse JSON

Logan_136080

Hey All,
I'm pretty new to catalytic and am still learning my way around the best modules for situations. I have worked with other automation software and built javascript apps. In other apps traversing JSON has been pretty straight forward but I'm having a hard time getting it to work the way I want in Catalytic. For instance, anytime I have a JSON that contains an object or array that object or array is put into a table automatically so I have to access the table instead of tapping into the JSON directly. I'm trying to learn to use the field formulas module to use JS to interact with fields but am still learning how to use JS within catalytic. The current issue I'm running into is I get a JSON response back that contains an array of 3 objects. In all situations, I just want to grab the first object and grab the ID data from that first object. I tried just using the field formula module with JS to do so and it didn't work. When I tried to use the lookup column field It was a struggle because there isn't a unique value for me to search by. I just need to grab the first record.
Example of the JSON

[
{"id":"13769","recordtype":"customer"},
{"id":"14709","recordtype":"customer"},
{"id":"14817","recordtype":"customer"}
]

I'm sure a lot of this is just being new to the system and not knowing the best modules and syntax to use for catalytic in order to work with JSON so I'm looking forward to hearing back and getting some tips!

tl;dr:

• What are the best tips for working with JSON in catalytic?
• With Catalytic, how do I grab the first item of an array when I don't have a unique value to grab it from the table it's put into.

Jeff_146001

@Logan_136080 Good question! Given your development background, I'm going to give you a couple different options:

• Stick to using JS to work with JSON directly
• Tips on using Data Tables

Stick to using JS to work with JSON directly

Is the JSON being output from one of the Web API actions (GET, POST, etc.)? If so, there's an option to return the raw JSON response, rather than converting it into fields and tables. When configuring the Web API action, for Response Mapping, choose Raw or Both instead of Fields. This will return a field that you can reference in the Fields: Field Formula action like:

fields["your-output-field--rawresponse"]

Tips on using Data Tables

Catalytic has over 25 Data Table actions that can be useful for reading, updating, and manipulating tables.

We actually don't currently have a "get first row" action, but you could upvote the idea for that.

You can pretty easily return the first row by following these steps, though:
1. Add the Tables: Look up data in a column action.
2. Enter the name of any column for Lookup column name, id in your case.
3. Enter . as the Search criteria andREGEX as the Advanced search type to return the first row that has any character.

Does that answer your question? Let us know if not--we're happy to help.

Logan_136080

@Jeff_291769 Thanks so much for the post! This is very helpful. How robust can the javascript be in the field formula module?

For some reason my field formula JS doesn't seem to be parsing anything out. I have it working in one module, but not in another

fields["customer"]:
[{"id":"13769","recordtype":"customer"},{"id":"14709","recordtype":"customer"},{"id":"14817","recordtype":"customer"},{"id":"15019","recordtype":"customer"}]
JS:
if(fields["customer"][0]){
result = fields["customer"][0].id
} else {
result = fields["create-customer-id"]["id"]
}

The javascript is correct, but for some reason, it's not pulling anything. The dependencies are all correct. I don't understand why it's not working. Is there something I'm missing in interacting with catalytic here?

Logan_136080

@Jeff_291769 Hey, Jeff, wanted to follow up with you. It seems as if when I use the raw response it's interpreting it as a string and I can't JSON.parse it to JSON for fields: formula to work with it. In the example above fields["customer"][0] is equal to "[" rather than "{"id":"13769","recordtype":"customer"}"

Tom_121527

Hi @Logan_136080, if you change fields["customer"][0] to {{customer}}[0] you should get the behavior you're expecting. Using the handlebar syntax, the customer field should be a JavaScript array when the field formula runs. Using the fields/bracket syntax, it will be the JSON string.

If you use the JavaScript typeof operator in a field formula, you should be able to verify this distinction on your end as well.

typeof fields["customer"] // string
typeof {{customer}} // object

When you type {{ in the field formula you should see a handy picker popup for searching and inserting available fields. Or you can type it out manually.

Logan_136080

@Tom_121527 Hey Tom, so looks like I'm able to get the data with it which is good, but now the system doesn't work with any if else conditional statements while working with the data. The module breaks unless I take the if/else out. Trying to figure the best approach, but will probably just have to make a bulkier solution. Will post my solution when I finally get it to work. Still open to more feedback if anyone has any.

Tom_121527

@Logan_136080 I tested out putting a {{ field reference inside an if statement and it's working for me. I have a guess that the JavaScript engine might be throwing an error when you try to access the zero index of the customer field when the field does not exist.
I recommend trying something like this...

var result;
// fallback to empty object when fields do not exist
var customer = {{customer}} || {};
var createCustomerId = {{create-customer-id}} || {};
if (customer[0]) {
  result = customer[0].id
} else if (createCustomerId) {
  result = createCustomerId.id
}

Hopefully that helps!

Logan_136080

@Tom_121527 I tried to changing the code to react based on the value be undefined and that didn't work, but your solution will probably work better! how robust of javascript can I write in the field formulas module? Can I write functions and call them within the field? and not necessarily going to do this but could I use ajax within and do deeper data processing?

Tom_121527

@Logan_136080 You can add pretty much any standard JavaScript to your field formulas. You can write functions and call them but you can not make network requests from field formulas.