Can’t get this config to load properly…
- I am using the DBConnector action in a CXone Studio script to query a Postgres table via
SELECT id, name FROM agents WHERE status = 'active'.
- The connection string uses the internal VPC endpoint and the credentials are confirmed working in psql.
- The
Result object returns { rows: [] } even though I can see 50 active rows in the DB console.
- Is there a specific permission scope or JSON mapping issue in the Studio snippet that causes silent failures on read operations?
Have you tried validating the column mapping configuration within the Studio DBConnector action? The empty result set usually stems from a mismatch between the SQL schema and the expected JSON output structure in the integration profile.
I encountered similar behavior when building custom data sources for Grafana. The DBConnector does not automatically infer field types. You must explicitly map the source columns to the target variables. Ensure your integration profile defines the fields exactly as they appear in the Postgres response. If id is a BIGINT in Postgres but defined as STRING in the Studio mapping, the connector may silently drop the row.
Check the raw HTTP response payload in the Studio debug logs. The connector returns a standard JSON array. If the structure differs, the parser fails. Here is the correct mapping pattern for a simple query:
{
"fields": [
{
"sourceName": "id",
"targetName": "agentId",
"dataType": "NUMBER"
},
{
"sourceName": "name",
"targetName": "agentName",
"dataType": "STRING"
}
]
}
Also, verify that the SQL query does not return more than the default batch size limit. Studio actions often truncate results if the payload exceeds internal thresholds. Add a LIMIT 10 clause to your SELECT statement for testing. If the result set populates, the issue is likely a timeout or size constraint rather than a permission scope.
The analytics:reports:read scope mentioned in other threads is irrelevant here. This is a direct database connection via VPC. The issue is almost certainly in the integration profile’s field definition or the SQL syntax handling of reserved keywords. Double-check that status is not a reserved word in your specific Postgres version, though it usually is not. Use quotes if necessary: SELECT id, name FROM agents WHERE "status" = 'active'.
Run the query in psql with the exact credentials and VPC endpoint used by Studio. If it works there, the Studio mapping is your bottleneck. Inspect the debug trace for MappingError or TypeMismatch codes. These are often hidden in the verbose logs.
I think the DBConnector in CXone Studio does not handle implicit type coercion for result sets the way a standard ORM might. The documentation for the NICE CXone Integration Platform states, “The connector requires explicit schema definition to map relational data to the internal variable store.” This explains why you are getting an empty array despite valid SQL execution. The query likely runs, but the mapping layer fails to parse the rows into the expected JSON structure, resulting in a silent drop.
You need to verify the resultMapping configuration in your integration profile. It is not enough to just have the columns exist. You must define the target variable structure explicitly.
{
"resultMapping": {
"rows": {
"id": {
"type": "integer",
"sourceColumn": "id"
},
"name": {
"type": "string",
"sourceColumn": "name"
}
}
}
}
Also, check the OAuth scopes attached to the service account running the DBConnector. While the VPC connectivity is valid, the integration service might lack the integration:read or data:query scopes required to materialize the result set into the session variables. The 401 or mapping error is often masked as an empty result if the permissions are insufficient for the write-back phase of the mapping.
I also noticed in recent posts that Postgres drivers in these connectors can be sensitive to timezone settings. Ensure your status column comparison matches the exact string format stored in the DB, including case sensitivity. If status is a boolean in Postgres, you might need to cast it in the SQL or adjust the mapping type to boolean instead of string.
Try adding a debug log step immediately after the DBConnector action to inspect the raw response object before it is mapped. This will tell you if the rows are arriving but failing the schema validation.
the documentation actually says cxone studio dbconnectors are strictly read-only. do not attempt to pass write commands via sql strings or you risk corrupting the integration state. use the dedicated update actions instead.
The problem is that the integration profile schema definition was likely missing the explicit column mapping, causing the parser to discard the rows as malformed. The docs state “The connector requires explicit schema definition to map relational data to the internal variable store,” so you must define the field types in the profile before querying.