Hey folks,
I’ve been wrestling with a Studio script that needs to pull customer data from an external SQL database using the DBConnector action. The DB call itself works fine. I get a 200 OK response and the JSON payload comes back exactly as expected. The issue is how Studio handles the response when I try to assign the fields to variables.
Here’s the payload I’m getting back from the DBConnector:
{
"results": [
{
"cust_id": "12345",
"vip_status": true,
"preferred_channel": "email"
}
]
}
I’m using the ASSIGN step to map $.results[0].vip_status to a variable called isVIP. If the record exists, this works perfectly. The problem arises when the customer doesn’t exist in the DB. The DBConnector returns an empty array [] instead of null or a single object. When I try to access $.results[0].vip_status on an empty array, Studio throws a generic runtime error and the script halts. It doesn’t gracefully handle the missing index.
I’ve tried wrapping the ASSIGN in an IF statement to check if $.results.length > 0, but Studio’s expression language seems to choke on .length for JSON arrays returned from REST calls. It treats the response as an object, not a native array type.
Is there a standard pattern for checking if a DBConnector result set is empty before trying to drill into the first record? I don’t want to add a try/catch block for every single field assignment just to handle the edge case where the user isn’t in the DB.
Also, I noticed the DBConnector action has a “Map Response” checkbox. I’ve been leaving it unchecked to get the raw JSON, but maybe I should be using that to flatten the structure? Just not sure if that helps with the empty array scenario.
Any tips on handling this without breaking the flow?