CXone Studio DBConnector JSON Syntax for Nested Field Lookup

Can anyone clarify the correct JSON structure for a DBConnector action when querying a nested attribute?

Background

I am migrating a legacy lookup flow from a custom REST endpoint to a native CXone Studio script. The target data resides in a DynamoDB table where the key is a simple ID, but the value is a complex JSON object. I need to extract a specific nested field (metadata.region) for downstream logic.

Issue

The DBConnector action seems to treat the result as a flat map. When I reference metadata.region in the subsequent ASSIGN action, it returns null. The raw JSON payload from the connector looks like this:

{
 "id": "usr_123",
 "metadata": {
 "region": "sa-east-1",
 "tier": "gold"
 }
}

I have tried referencing it as result.metadata.region and result["metadata"]["region"], but neither works in the Studio expression evaluator. Is there a specific flattening behavior I am missing, or should I be using a different connector type for nested structures?

Troubleshooting

• Verified the DynamoDB item exists and is accessible via direct API call.
• Checked Studio logs; the DBConnector returns a 200 OK with the full JSON body.
• Confirmed that the variable name in the ASSIGN action matches the DBConnector output variable exactly.

Oh, this is a known issue with nested object handling in the DBConnector action. The standard JSON parser in the script step often flattens or ignores deep paths if not explicitly mapped. You need to ensure the query parameters are structured correctly to drill down into the metadata object.

Here is how i handle this in my architect flows using a Data Action instead of the raw script, as it gives better control over the mapping.

Problem:
The DBConnector action fails to return the nested region value because the query string expects a flat key-value pair, but the DynamoDB item contains a nested JSON structure.

Code Solution:
Use the FindFirst expression within a Data Action configuration to map the result. Do not pass the raw JSON string. Instead, use this mapping in the Data Action inputs:

{
 "TableName": "CustomerData",
 "Key": {
 "id": {
 "S": "{{ToString(trigger.contactId)}}"
 }
 }
}

Then, in the output mapping of the Data Action, use this expression to extract the nested field:

{{ToString(FindFirst(result.Items, "id", trigger.contactId).metadata.region)}}

Error Handling:
If the item is not found, FindFirst returns null. Always wrap this in a conditional check to avoid flow failures.

{{IsNullOrEmpty(FindFirst(result.Items, "id", trigger.contactId)) ? "DEFAULT_REGION" : ToString(FindFirst(result.Items, "id", trigger.contactId).metadata.region)}}

This approach bypasses the JSON parsing issues in the Studio script and leverages the robust expression engine. The key is ensuring the input to the Data Action is a valid DynamoDB Key object, not a generic JSON blob.

Question: Does your DynamoDB table use a composite key? If so, you must include the sort key in the Key object above, otherwise the query will fail with a ValidationException.