Using a Database View to Expose Clean Data to Third-Party Systems via the ServiceNow Table

[Selva Arun]

Using a Database View to Expose Clean Data to Third-Party Systems via the ServiceNow Table REST API

Why I wrote this

I did my best to search the ServiceNow Community and official documentation before writing this, and I did not come across anything that covered this exact combination of issues. If something similar already exists and I missed it, I apologize, but I hope this is still useful to someone.

About a month ago I was working on connecting a third-party AI governance platform to ServiceNow. The platform was in its early stages of building out its ServiceNow integration and already had a set of pre-configured connector types for standard ServiceNow modules: service catalog submissions, AI control tower assets, and a few others. But it also offered something called a Mapped View option, which was designed specifically for client-built Database Views. The idea was straightforward: you build the view on the ServiceNow side, point the connector at it, load the columns, and map them to the fields the platform expects.

Because our data lived in a custom scoped application that did not match any of the pre-configured connector types, the Mapped View option was the right fit. It gave us full control over what fields to expose and how to structure them, without needing the platform to build a dedicated connector for our specific tables. What I did not expect were the two issues I ran into along the way. This article walks through both of them so you have what you need before you start.

The problem with reference fields and external integrations

When an external system calls the ServiceNow Table REST API it typically sends a GET request like this:

GET /api/now/table/{tableName}?sysparm_fields=field1,field2

For plain string or date fields this works exactly as expected. The problem shows up with reference fields. A reference field like opened_by does not store a person's name: it stores a sys_id. When the external system reads that field it gets the sys_id, and unless it knows how to resolve that back to a name it will display it as is.

ServiceNow does expose a display value when you add sysparm_display_value=all to the request, but not all third-party connectors give you control over that parameter. Even when they do, you are relying on the external system to correctly pick display_value over value for every reference field. In my case the platform was reading value by default and there was no per-field configuration option to change that behavior. The Owner field was showing a raw sys_id instead of a person's name.

The solution: a Database View with a sys_user join

The approach that worked cleanly was adding a sys_user join to the Database View. A Database View in ServiceNow lets you join multiple tables and expose the combined result as if it were a single table. Because it behaves like a table you can query it through the Table REST API using the same endpoint pattern with no custom APIs or middleware.

The key idea is this: instead of exposing the reference field itself (which returns a sys_id), you join sys_user into the view and expose usr_name as a plain string column. The external system reads usr_name and gets "Jane Smith" instead of "1a2b3c4d...".

Navigate to System Definition: Database Views and open your view (or create a new one). In the View Tables related list, add a new row with the following values:

Field	Value
Table	sys_user
Order	400
Active	true
Left Join	true
Variable Prefix	usr
Where Clause	usr_sys_id = [your_reference_field]

Replace [your_reference_field] with the field in your primary table that holds the sys_id, for example opened_by.

The variable prefix usr means every column from sys_user will appear in the view as usr_name, usr_email, usr_sys_id, and so on. These are plain string columns with no reference object wrapping. I used a LEFT JOIN rather than an inner join so that records with no related user record still appear in the view. An inner join would silently drop those records, which is rarely what you want for an outbound integration.

Checking access before you test

Before testing the view through the REST API, verify that your integration service account has read access to the sys_user table. Database Views are resolved at query time, and if the calling account cannot read sys_user, the join will silently return empty values for all usr_ columns without throwing an error.

Run this background script in a non-production instance (Global scope, read-only) to audit what roles are required on sys_user:

var acl = new GlideRecord("sys_security_acl");
acl.addQuery("name", "STARTSWITH", "sys_user");
acl.orderBy("operation");
acl.query();

while (acl.next()) {
    var line = "name=" + acl.getValue("name") + " | op=" + acl.getValue("operation") + " | active=" + acl.getValue("active");

    var roleRel = new GlideRecord("sys_security_acl_role");
    roleRel.addQuery("sys_security_acl", acl.getUniqueValue());
    roleRel.query();

    var roles = "";
    while (roleRel.next()) {
        roles = roles + roleRel.getDisplayValue("sys_user_role") + " ";
    }

    gs.info(line);
    gs.info("  roles: " + (roles.length > 0 ? roles : "none"));
}

Check the output and confirm your service account holds at least one of the roles listed for the sys_user read ACL. If it does not, work with your security team to assign the appropriate read role before proceeding.

Verifying the view in REST API Explorer

Once the view is saved and the service account is confirmed, test it directly in the REST API Explorer before connecting the external system. This step saves a significant amount of troubleshooting later.

Send a GET to your view with a small field list:

GET /api/now/table/your_view_name
    ?sysparm_fields=your_reference_field,usr_name,usr_email
    &sysparm_limit=5

In the response you should see usr_name returning a plain string like "Jane Smith" next to your reference field returning the reference object with the sys_id. That confirms the join is resolving correctly and the external system will receive the name it needs.

The gotcha: empty choice fields return null, not an empty string

After the view was working I connected the external platform and ran into an error during the schema loading step:

Invalid input: expected string, received null
path: result[0].your_choice_field.display_value

The platform was requesting data with sysparm_display_value=all, which tells ServiceNow to return both the stored value and the display label for every field. For most fields this works fine. But for choice fields that have no value set, ServiceNow returns this:

"your_choice_field": {
  "value": "",
  "display_value": null
}

Not an empty string: null. The platform's schema validation was expecting a string type for display_value and failed when it received null.

Important: This is standard ServiceNow behavior, not a bug in your view. ServiceNow has no choice label to resolve for an empty value so it returns null rather than an empty string. The fix belongs on the third-party connector side: the connector should null-coalesce display_value to an empty string before running schema validation.

If you hit this error with your own integration vendor, the fastest workaround to ask for is a connector setting to use sysparm_display_value=true instead of all. With sysparm_display_value=true, ServiceNow returns display values as plain strings directly rather than as objects. The {value, display_value} wrapper disappears entirely, so there is no null to trip over.

Summary

Database Views are a clean, low-code pattern for exposing ServiceNow data to external systems through the Table REST API. They are especially useful when the third-party platform already supports a mapped or custom view option and you need to surface data from a scoped application that does not match any pre-configured connector type.

The three things worth keeping in mind from this experience are:

First: Reference fields return a sys_id as their value. Join sys_user into your view to expose a plain string name column instead.

Second: Check that your service account has read access to every table you join into the view, not just the primary table. A missing role returns empty columns silently.

Third: If your integration vendor uses sysparm_display_value=all, expect empty choice fields to return null for display_value. Confirm the vendor handles this before you go to production.

I tested all of this in a non-production instance first and verified through REST API Explorer before connecting the external system. That sequence saved a significant amount of troubleshooting time and I would recommend it for any integration of this kind.

Hope this helps someone else who runs into the same setup. Happy to answer questions in the comments.

Thank you,

Selva(Meena) Arun